486 lines
18 KiB
Transact-SQL
486 lines
18 KiB
Transact-SQL
/* ============================================================
|
|
Compare Script (Schema + Rowcounts + Programmable Objects)
|
|
SQL Server 2022
|
|
|
|
Compares SOURCE and TARGET databases without comparing row contents:
|
|
- Rowcount per table (diff list + summary)
|
|
- Tables/columns (datatype/length/nullable/collation) diff list + summary
|
|
- Programmable objects (views/procs/functions) via SHA2_256 hash of definition
|
|
- Triggers via SHA2_256 hash of definition
|
|
- Users/Roles existence + role memberships (name-based)
|
|
|
|
Output:
|
|
- Textual progress and summary via RAISERROR ... WITH NOWAIT
|
|
- Result sets with detailed diffs
|
|
|
|
Configure DB names below.
|
|
============================================================ */
|
|
|
|
SET NOCOUNT ON;
|
|
SET XACT_ABORT ON;
|
|
|
|
DECLARE @SourceDb sysname = N'sysdb_UTF8';
|
|
DECLARE @TargetDb sysname = N'sysdb_utf8_jr';
|
|
|
|
DECLARE @QSource sysname = QUOTENAME(@SourceDb);
|
|
DECLARE @QTarget sysname = QUOTENAME(@TargetDb);
|
|
|
|
DECLARE @sev int = 10;
|
|
DECLARE @Now nvarchar(30);
|
|
|
|
DECLARE @msg nvarchar(4000);
|
|
SET @Now = CONVERT(nvarchar(30), SYSDATETIME(), 121);
|
|
SET @msg = CONCAT(@Now, N' | COMPARE_START | Source=', @SourceDb, N' | Target=', @TargetDb);
|
|
RAISERROR(@msg, @sev, 1) WITH NOWAIT;
|
|
|
|
--------------------------------------------------------------------------------
|
|
-- 1) Rowcounts per table
|
|
--------------------------------------------------------------------------------
|
|
SET @Now = CONVERT(nvarchar(30), SYSDATETIME(), 121);
|
|
RAISERROR(CONCAT(@Now, N' | STEP 1/5 | Rowcounts per table...'), @sev, 1) WITH NOWAIT;
|
|
|
|
IF OBJECT_ID('tempdb..#RowCounts') IS NOT NULL DROP TABLE #RowCounts;
|
|
CREATE TABLE #RowCounts(
|
|
DbName sysname NOT NULL,
|
|
SchemaName sysname NOT NULL,
|
|
TableName sysname NOT NULL,
|
|
RowCount bigint NOT NULL
|
|
);
|
|
|
|
DECLARE @sql nvarchar(max);
|
|
|
|
SET @sql = N'
|
|
INSERT INTO #RowCounts(DbName, SchemaName, TableName, RowCount)
|
|
SELECT N''' + REPLACE(@SourceDb,'''','''''') + N''', s.name, t.name, SUM(p.rows)
|
|
FROM ' + @QSource + N'.sys.tables t
|
|
JOIN ' + @QSource + N'.sys.schemas s ON s.schema_id = t.schema_id
|
|
JOIN ' + @QSource + N'.sys.partitions p ON p.object_id = t.object_id AND p.index_id IN (0,1)
|
|
WHERE t.is_ms_shipped=0
|
|
GROUP BY s.name, t.name;';
|
|
EXEC sp_executesql @sql;
|
|
|
|
SET @sql = N'
|
|
INSERT INTO #RowCounts(DbName, SchemaName, TableName, RowCount)
|
|
SELECT N''' + REPLACE(@TargetDb,'''','''''') + N''', s.name, t.name, SUM(p.rows)
|
|
FROM ' + @QTarget + N'.sys.tables t
|
|
JOIN ' + @QTarget + N'.sys.schemas s ON s.schema_id = t.schema_id
|
|
JOIN ' + @QTarget + N'.sys.partitions p ON p.object_id = t.object_id AND p.index_id IN (0,1)
|
|
WHERE t.is_ms_shipped=0
|
|
GROUP BY s.name, t.name;';
|
|
EXEC sp_executesql @sql;
|
|
|
|
;WITH s AS (
|
|
SELECT SchemaName, TableName, RowCount FROM #RowCounts WHERE DbName=@SourceDb
|
|
),
|
|
t AS (
|
|
SELECT SchemaName, TableName, RowCount FROM #RowCounts WHERE DbName=@TargetDb
|
|
),
|
|
d AS (
|
|
SELECT
|
|
COALESCE(s.SchemaName, t.SchemaName) AS SchemaName,
|
|
COALESCE(s.TableName, t.TableName) AS TableName,
|
|
s.RowCount AS SourceRows,
|
|
t.RowCount AS TargetRows,
|
|
(ISNULL(t.RowCount,0) - ISNULL(s.RowCount,0)) AS Diff
|
|
FROM s
|
|
FULL OUTER JOIN t
|
|
ON t.SchemaName = s.SchemaName
|
|
AND t.TableName = s.TableName
|
|
)
|
|
SELECT *
|
|
INTO #RowDiff
|
|
FROM d
|
|
WHERE ISNULL(SourceRows,-1) <> ISNULL(TargetRows,-1);
|
|
|
|
DECLARE @RowDiffCount int = (SELECT COUNT(*) FROM #RowDiff);
|
|
DECLARE @RowTotalSource int = (SELECT COUNT(*) FROM (SELECT DISTINCT SchemaName, TableName FROM #RowCounts WHERE DbName=@SourceDb) x);
|
|
DECLARE @RowTotalTarget int = (SELECT COUNT(*) FROM (SELECT DISTINCT SchemaName, TableName FROM #RowCounts WHERE DbName=@TargetDb) x);
|
|
|
|
SET @Now = CONVERT(nvarchar(30), SYSDATETIME(), 121);
|
|
RAISERROR(CONCAT(@Now, N' | ROWCOUNTS | SourceTables=', @RowTotalSource, N' TargetTables=', @RowTotalTarget, N' TablesWithRowDiff=', @RowDiffCount), @sev, 1) WITH NOWAIT;
|
|
|
|
IF @RowDiffCount = 0
|
|
RAISERROR(CONCAT(CONVERT(nvarchar(30), SYSDATETIME(), 121), N' | ROWCOUNTS_OK | All table rowcounts match.'), @sev, 1) WITH NOWAIT;
|
|
ELSE
|
|
BEGIN
|
|
RAISERROR(CONCAT(CONVERT(nvarchar(30), SYSDATETIME(), 121), N' | ROWCOUNTS_DIFF | Showing tables with rowcount differences...'), @sev, 1) WITH NOWAIT;
|
|
SELECT * FROM #RowDiff ORDER BY ABS(Diff) DESC, SchemaName, TableName;
|
|
END
|
|
|
|
--------------------------------------------------------------------------------
|
|
-- 2) Column/schema diffs
|
|
--------------------------------------------------------------------------------
|
|
SET @Now = CONVERT(nvarchar(30), SYSDATETIME(), 121);
|
|
RAISERROR(CONCAT(@Now, N' | STEP 2/5 | Table/column schema diffs...'), @sev, 1) WITH NOWAIT;
|
|
|
|
IF OBJECT_ID('tempdb..#Cols') IS NOT NULL DROP TABLE #Cols;
|
|
CREATE TABLE #Cols(
|
|
DbName sysname,
|
|
SchemaName sysname,
|
|
TableName sysname,
|
|
ColumnName sysname,
|
|
TypeName sysname,
|
|
MaxLen int,
|
|
PrecisionVal int,
|
|
ScaleVal int,
|
|
IsNullable bit,
|
|
CollationName sysname NULL
|
|
);
|
|
|
|
SET @sql = N'
|
|
INSERT #Cols
|
|
SELECT N''' + REPLACE(@SourceDb,'''','''''') + N''',
|
|
s.name, t.name, c.name,
|
|
ty.name,
|
|
c.max_length,
|
|
c.precision,
|
|
c.scale,
|
|
c.is_nullable,
|
|
c.collation_name
|
|
FROM ' + @QSource + N'.sys.columns c
|
|
JOIN ' + @QSource + N'.sys.tables t ON t.object_id=c.object_id
|
|
JOIN ' + @QSource + N'.sys.schemas s ON s.schema_id=t.schema_id
|
|
JOIN ' + @QSource + N'.sys.types ty ON ty.user_type_id=c.user_type_id
|
|
WHERE t.is_ms_shipped=0;';
|
|
EXEC sp_executesql @sql;
|
|
|
|
SET @sql = N'
|
|
INSERT #Cols
|
|
SELECT N''' + REPLACE(@TargetDb,'''','''''') + N''',
|
|
s.name, t.name, c.name,
|
|
ty.name,
|
|
c.max_length,
|
|
c.precision,
|
|
c.scale,
|
|
c.is_nullable,
|
|
c.collation_name
|
|
FROM ' + @QTarget + N'.sys.columns c
|
|
JOIN ' + @QTarget + N'.sys.tables t ON t.object_id=c.object_id
|
|
JOIN ' + @QTarget + N'.sys.schemas s ON s.schema_id=t.schema_id
|
|
JOIN ' + @QTarget + N'.sys.types ty ON ty.user_type_id=c.user_type_id
|
|
WHERE t.is_ms_shipped=0;';
|
|
EXEC sp_executesql @sql;
|
|
|
|
;WITH s AS (SELECT * FROM #Cols WHERE DbName=@SourceDb),
|
|
t AS (SELECT * FROM #Cols WHERE DbName=@TargetDb),
|
|
d AS (
|
|
SELECT
|
|
COALESCE(s.SchemaName, t.SchemaName) AS SchemaName,
|
|
COALESCE(s.TableName, t.TableName) AS TableName,
|
|
COALESCE(s.ColumnName, t.ColumnName) AS ColumnName,
|
|
CONCAT(ISNULL(s.TypeName,N''), N'(', ISNULL(CONVERT(nvarchar(20),s.MaxLen),N''), N')') AS SourceType,
|
|
CONCAT(ISNULL(t.TypeName,N''), N'(', ISNULL(CONVERT(nvarchar(20),t.MaxLen),N''), N')') AS TargetType,
|
|
s.IsNullable AS SourceNullable,
|
|
t.IsNullable AS TargetNullable,
|
|
s.CollationName AS SourceCollation,
|
|
t.CollationName AS TargetCollation
|
|
FROM s
|
|
FULL OUTER JOIN t
|
|
ON t.SchemaName = s.SchemaName
|
|
AND t.TableName = s.TableName
|
|
AND t.ColumnName = s.ColumnName
|
|
)
|
|
SELECT *
|
|
INTO #ColDiff
|
|
FROM d
|
|
WHERE
|
|
ISNULL(SourceType,N'') <> ISNULL(TargetType,N'')
|
|
OR ISNULL(SourceNullable,2) <> ISNULL(TargetNullable,2)
|
|
OR ISNULL(SourceCollation,N'') <> ISNULL(TargetCollation,N'');
|
|
|
|
DECLARE @ColDiffCount int = (SELECT COUNT(*) FROM #ColDiff);
|
|
RAISERROR(CONCAT(CONVERT(nvarchar(30), SYSDATETIME(), 121), N' | COLUMNS | ColumnDiffs=', @ColDiffCount), @sev, 1) WITH NOWAIT;
|
|
|
|
IF @ColDiffCount = 0
|
|
RAISERROR(CONCAT(CONVERT(nvarchar(30), SYSDATETIME(), 121), N' | COLUMNS_OK | No column definition diffs detected.'), @sev, 1) WITH NOWAIT;
|
|
ELSE
|
|
BEGIN
|
|
RAISERROR(CONCAT(CONVERT(nvarchar(30), SYSDATETIME(), 121), N' | COLUMNS_DIFF | Showing column diffs...'), @sev, 1) WITH NOWAIT;
|
|
SELECT * FROM #ColDiff ORDER BY SchemaName, TableName, ColumnName;
|
|
END
|
|
|
|
--------------------------------------------------------------------------------
|
|
-- 3) Programmable objects
|
|
--------------------------------------------------------------------------------
|
|
RAISERROR(CONCAT(CONVERT(nvarchar(30), SYSDATETIME(), 121), N' | STEP 3/5 | Programmable objects (hash compare)...'), @sev, 1) WITH NOWAIT;
|
|
|
|
IF OBJECT_ID('tempdb..#Modules') IS NOT NULL DROP TABLE #Modules;
|
|
CREATE TABLE #Modules(
|
|
DbName sysname,
|
|
ObjectType nvarchar(60),
|
|
SchemaName sysname,
|
|
ObjectName sysname,
|
|
DefinitionHash varbinary(32) NULL
|
|
);
|
|
|
|
SET @sql = N'
|
|
INSERT #Modules
|
|
SELECT
|
|
N''' + REPLACE(@SourceDb,'''','''''') + N''',
|
|
o.type_desc,
|
|
s.name,
|
|
o.name,
|
|
HASHBYTES(''SHA2_256'', CONVERT(varbinary(max), m.definition))
|
|
FROM ' + @QSource + N'.sys.objects o
|
|
JOIN ' + @QSource + N'.sys.schemas s ON s.schema_id=o.schema_id
|
|
JOIN ' + @QSource + N'.sys.sql_modules m ON m.object_id=o.object_id
|
|
WHERE o.is_ms_shipped=0
|
|
AND o.type IN (''V'',''P'',''FN'',''IF'',''TF'');';
|
|
EXEC sp_executesql @sql;
|
|
|
|
SET @sql = N'
|
|
INSERT #Modules
|
|
SELECT
|
|
N''' + REPLACE(@TargetDb,'''','''''') + N''',
|
|
o.type_desc,
|
|
s.name,
|
|
o.name,
|
|
HASHBYTES(''SHA2_256'', CONVERT(varbinary(max), m.definition))
|
|
FROM ' + @QTarget + N'.sys.objects o
|
|
JOIN ' + @QTarget + N'.sys.schemas s ON s.schema_id=o.schema_id
|
|
JOIN ' + @QTarget + N'.sys.sql_modules m ON m.object_id=o.object_id
|
|
WHERE o.is_ms_shipped=0
|
|
AND o.type IN (''V'',''P'',''FN'',''IF'',''TF'');';
|
|
EXEC sp_executesql @sql;
|
|
|
|
;WITH s AS (SELECT * FROM #Modules WHERE DbName=@SourceDb),
|
|
t AS (SELECT * FROM #Modules WHERE DbName=@TargetDb),
|
|
d AS (
|
|
SELECT
|
|
COALESCE(s.ObjectType, t.ObjectType) AS ObjectType,
|
|
COALESCE(s.SchemaName, t.SchemaName) AS SchemaName,
|
|
COALESCE(s.ObjectName, t.ObjectName) AS ObjectName,
|
|
CASE WHEN s.ObjectName IS NULL THEN 'MISSING_IN_SOURCE'
|
|
WHEN t.ObjectName IS NULL THEN 'MISSING_IN_TARGET'
|
|
WHEN s.DefinitionHash <> t.DefinitionHash THEN 'DIFFERENT_DEFINITION'
|
|
ELSE 'OK' END AS Status
|
|
FROM s
|
|
FULL OUTER JOIN t
|
|
ON t.ObjectType = s.ObjectType
|
|
AND t.SchemaName = s.SchemaName
|
|
AND t.ObjectName = s.ObjectName
|
|
)
|
|
SELECT *
|
|
INTO #ModuleDiff
|
|
FROM d
|
|
WHERE Status <> 'OK';
|
|
|
|
DECLARE @ModuleDiffCount int = (SELECT COUNT(*) FROM #ModuleDiff);
|
|
RAISERROR(CONCAT(CONVERT(nvarchar(30), SYSDATETIME(), 121), N' | MODULES | Differences=', @ModuleDiffCount), @sev, 1) WITH NOWAIT;
|
|
|
|
IF @ModuleDiffCount = 0
|
|
RAISERROR(CONCAT(CONVERT(nvarchar(30), SYSDATETIME(), 121), N' | MODULES_OK | No module definition diffs detected.'), @sev, 1) WITH NOWAIT;
|
|
ELSE
|
|
BEGIN
|
|
RAISERROR(CONCAT(CONVERT(nvarchar(30), SYSDATETIME(), 121), N' | MODULES_DIFF | Showing module diffs...'), @sev, 1) WITH NOWAIT;
|
|
SELECT * FROM #ModuleDiff ORDER BY ObjectType, SchemaName, ObjectName;
|
|
END
|
|
|
|
--------------------------------------------------------------------------------
|
|
-- 4) Triggers
|
|
--------------------------------------------------------------------------------
|
|
RAISERROR(CONCAT(CONVERT(nvarchar(30), SYSDATETIME(), 121), N' | STEP 4/5 | Triggers (hash compare)...'), @sev, 1) WITH NOWAIT;
|
|
|
|
IF OBJECT_ID('tempdb..#Triggers') IS NOT NULL DROP TABLE #Triggers;
|
|
CREATE TABLE #Triggers(
|
|
DbName sysname,
|
|
TriggerSchema sysname,
|
|
TriggerName sysname,
|
|
ParentSchema sysname,
|
|
ParentName sysname,
|
|
DefinitionHash varbinary(32) NULL
|
|
);
|
|
|
|
SET @sql = N'
|
|
INSERT #Triggers
|
|
SELECT
|
|
N''' + REPLACE(@SourceDb,'''','''''') + N''',
|
|
ss.name,
|
|
tr.name,
|
|
ps.name,
|
|
pt.name,
|
|
HASHBYTES(''SHA2_256'', CONVERT(varbinary(max), m.definition))
|
|
FROM ' + @QSource + N'.sys.triggers tr
|
|
JOIN ' + @QSource + N'.sys.tables pt ON pt.object_id = tr.parent_id
|
|
JOIN ' + @QSource + N'.sys.schemas ps ON ps.schema_id = pt.schema_id
|
|
JOIN ' + @QSource + N'.sys.schemas ss ON ss.schema_id = tr.schema_id
|
|
JOIN ' + @QSource + N'.sys.sql_modules m ON m.object_id = tr.object_id
|
|
WHERE tr.is_ms_shipped=0;';
|
|
EXEC sp_executesql @sql;
|
|
|
|
SET @sql = N'
|
|
INSERT #Triggers
|
|
SELECT
|
|
N''' + REPLACE(@TargetDb,'''','''''') + N''',
|
|
ss.name,
|
|
tr.name,
|
|
ps.name,
|
|
pt.name,
|
|
HASHBYTES(''SHA2_256'', CONVERT(varbinary(max), m.definition))
|
|
FROM ' + @QTarget + N'.sys.triggers tr
|
|
JOIN ' + @QTarget + N'.sys.tables pt ON pt.object_id = tr.parent_id
|
|
JOIN ' + @QTarget + N'.sys.schemas ps ON ps.schema_id = pt.schema_id
|
|
JOIN ' + @QTarget + N'.sys.schemas ss ON ss.schema_id = tr.schema_id
|
|
JOIN ' + @QTarget + N'.sys.sql_modules m ON m.object_id = tr.object_id
|
|
WHERE tr.is_ms_shipped=0;';
|
|
EXEC sp_executesql @sql;
|
|
|
|
;WITH s AS (SELECT * FROM #Triggers WHERE DbName=@SourceDb),
|
|
t AS (SELECT * FROM #Triggers WHERE DbName=@TargetDb),
|
|
d AS (
|
|
SELECT
|
|
COALESCE(s.TriggerSchema, t.TriggerSchema) AS TriggerSchema,
|
|
COALESCE(s.TriggerName, t.TriggerName) AS TriggerName,
|
|
COALESCE(s.ParentSchema, t.ParentSchema) AS ParentSchema,
|
|
COALESCE(s.ParentName, t.ParentName) AS ParentTable,
|
|
CASE WHEN s.TriggerName IS NULL THEN 'MISSING_IN_SOURCE'
|
|
WHEN t.TriggerName IS NULL THEN 'MISSING_IN_TARGET'
|
|
WHEN s.DefinitionHash <> t.DefinitionHash THEN 'DIFFERENT_DEFINITION'
|
|
ELSE 'OK' END AS Status
|
|
FROM s
|
|
FULL OUTER JOIN t
|
|
ON t.TriggerSchema = s.TriggerSchema
|
|
AND t.TriggerName = s.TriggerName
|
|
AND t.ParentSchema = s.ParentSchema
|
|
AND t.ParentName = s.ParentName
|
|
)
|
|
SELECT *
|
|
INTO #TriggerDiff
|
|
FROM d
|
|
WHERE Status <> 'OK';
|
|
|
|
DECLARE @TriggerDiffCount int = (SELECT COUNT(*) FROM #TriggerDiff);
|
|
RAISERROR(CONCAT(CONVERT(nvarchar(30), SYSDATETIME(), 121), N' | TRIGGERS | Differences=', @TriggerDiffCount), @sev, 1) WITH NOWAIT;
|
|
|
|
IF @TriggerDiffCount = 0
|
|
RAISERROR(CONCAT(CONVERT(nvarchar(30), SYSDATETIME(), 121), N' | TRIGGERS_OK | No trigger definition diffs detected.'), @sev, 1) WITH NOWAIT;
|
|
ELSE
|
|
BEGIN
|
|
RAISERROR(CONCAT(CONVERT(nvarchar(30), SYSDATETIME(), 121), N' | TRIGGERS_DIFF | Showing trigger diffs...'), @sev, 1) WITH NOWAIT;
|
|
SELECT * FROM #TriggerDiff ORDER BY TriggerSchema, TriggerName, ParentSchema, ParentTable;
|
|
END
|
|
|
|
--------------------------------------------------------------------------------
|
|
-- 5) Users/Roles
|
|
--------------------------------------------------------------------------------
|
|
RAISERROR(CONCAT(CONVERT(nvarchar(30), SYSDATETIME(), 121), N' | STEP 5/5 | Users/Roles (existence + memberships)...'), @sev, 1) WITH NOWAIT;
|
|
|
|
IF OBJECT_ID('tempdb..#Principals') IS NOT NULL DROP TABLE #Principals;
|
|
CREATE TABLE #Principals(
|
|
DbName sysname,
|
|
Name sysname,
|
|
TypeDesc nvarchar(60)
|
|
);
|
|
|
|
SET @sql = N'
|
|
INSERT #Principals
|
|
SELECT N''' + REPLACE(@SourceDb,'''','''''') + N''', name, type_desc
|
|
FROM ' + @QSource + N'.sys.database_principals
|
|
WHERE type IN (''S'',''U'',''G'',''R'')
|
|
AND name NOT IN (''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'');';
|
|
EXEC sp_executesql @sql;
|
|
|
|
SET @sql = N'
|
|
INSERT #Principals
|
|
SELECT N''' + REPLACE(@TargetDb,'''','''''') + N''', name, type_desc
|
|
FROM ' + @QTarget + N'.sys.database_principals
|
|
WHERE type IN (''S'',''U'',''G'',''R'')
|
|
AND name NOT IN (''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'');';
|
|
EXEC sp_executesql @sql;
|
|
|
|
;WITH s AS (SELECT * FROM #Principals WHERE DbName=@SourceDb),
|
|
t AS (SELECT * FROM #Principals WHERE DbName=@TargetDb),
|
|
d AS (
|
|
SELECT
|
|
COALESCE(s.Name, t.Name) AS PrincipalName,
|
|
s.TypeDesc AS SourceType,
|
|
t.TypeDesc AS TargetType,
|
|
CASE WHEN s.Name IS NULL THEN 'MISSING_IN_SOURCE'
|
|
WHEN t.Name IS NULL THEN 'MISSING_IN_TARGET'
|
|
WHEN s.TypeDesc <> t.TypeDesc THEN 'TYPE_DIFF'
|
|
ELSE 'OK' END AS Status
|
|
FROM s
|
|
FULL OUTER JOIN t ON t.Name = s.Name
|
|
)
|
|
SELECT *
|
|
INTO #PrincipalDiff
|
|
FROM d
|
|
WHERE Status <> 'OK';
|
|
|
|
DECLARE @PrincipalDiffCount int = (SELECT COUNT(*) FROM #PrincipalDiff);
|
|
RAISERROR(CONCAT(CONVERT(nvarchar(30), SYSDATETIME(), 121), N' | PRINCIPALS | Differences=', @PrincipalDiffCount), @sev, 1) WITH NOWAIT;
|
|
|
|
IF @PrincipalDiffCount > 0
|
|
BEGIN
|
|
RAISERROR(CONCAT(CONVERT(nvarchar(30), SYSDATETIME(), 121), N' | PRINCIPALS_DIFF | Showing principal diffs...'), @sev, 1) WITH NOWAIT;
|
|
SELECT * FROM #PrincipalDiff ORDER BY PrincipalName;
|
|
END
|
|
ELSE
|
|
RAISERROR(CONCAT(CONVERT(nvarchar(30), SYSDATETIME(), 121), N' | PRINCIPALS_OK | No principal diffs detected.'), @sev, 1) WITH NOWAIT;
|
|
|
|
IF OBJECT_ID('tempdb..#RoleMembers') IS NOT NULL DROP TABLE #RoleMembers;
|
|
CREATE TABLE #RoleMembers(
|
|
DbName sysname,
|
|
RoleName sysname,
|
|
MemberName sysname
|
|
);
|
|
|
|
SET @sql = N'
|
|
INSERT #RoleMembers
|
|
SELECT N''' + REPLACE(@SourceDb,'''','''''') + N''',
|
|
r.name AS RoleName, m.name AS MemberName
|
|
FROM ' + @QSource + N'.sys.database_role_members rm
|
|
JOIN ' + @QSource + N'.sys.database_principals r ON r.principal_id = rm.role_principal_id
|
|
JOIN ' + @QSource + N'.sys.database_principals m ON m.principal_id = rm.member_principal_id
|
|
WHERE r.name NOT IN (''public'');';
|
|
EXEC sp_executesql @sql;
|
|
|
|
SET @sql = N'
|
|
INSERT #RoleMembers
|
|
SELECT N''' + REPLACE(@TargetDb,'''','''''') + N''',
|
|
r.name AS RoleName, m.name AS MemberName
|
|
FROM ' + @QTarget + N'.sys.database_role_members rm
|
|
JOIN ' + @QTarget + N'.sys.database_principals r ON r.principal_id = rm.role_principal_id
|
|
JOIN ' + @QTarget + N'.sys.database_principals m ON m.principal_id = rm.member_principal_id
|
|
WHERE r.name NOT IN (''public'');';
|
|
EXEC sp_executesql @sql;
|
|
|
|
;WITH s AS (SELECT RoleName, MemberName FROM #RoleMembers WHERE DbName=@SourceDb),
|
|
t AS (SELECT RoleName, MemberName FROM #RoleMembers WHERE DbName=@TargetDb),
|
|
d AS (
|
|
SELECT
|
|
COALESCE(s.RoleName, t.RoleName) AS RoleName,
|
|
COALESCE(s.MemberName, t.MemberName) AS MemberName,
|
|
CASE WHEN s.RoleName IS NULL THEN 'MISSING_IN_SOURCE'
|
|
WHEN t.RoleName IS NULL THEN 'MISSING_IN_TARGET'
|
|
ELSE 'OK' END AS Status
|
|
FROM s
|
|
FULL OUTER JOIN t
|
|
ON t.RoleName = s.RoleName AND t.MemberName = s.MemberName
|
|
)
|
|
SELECT *
|
|
INTO #RoleMemberDiff
|
|
FROM d
|
|
WHERE Status <> 'OK';
|
|
|
|
DECLARE @RoleMemberDiffCount int = (SELECT COUNT(*) FROM #RoleMemberDiff);
|
|
RAISERROR(CONCAT(CONVERT(nvarchar(30), SYSDATETIME(), 121), N' | ROLEMEMBERS | Differences=', @RoleMemberDiffCount), @sev, 1) WITH NOWAIT;
|
|
|
|
IF @RoleMemberDiffCount > 0
|
|
BEGIN
|
|
RAISERROR(CONCAT(CONVERT(nvarchar(30), SYSDATETIME(), 121), N' | ROLEMEMBERS_DIFF | Showing role membership diffs...'), @sev, 1) WITH NOWAIT;
|
|
SELECT * FROM #RoleMemberDiff ORDER BY RoleName, MemberName;
|
|
END
|
|
ELSE
|
|
RAISERROR(CONCAT(CONVERT(nvarchar(30), SYSDATETIME(), 121), N' | ROLEMEMBERS_OK | No role membership diffs detected.'), @sev, 1) WITH NOWAIT;
|
|
|
|
RAISERROR(CONCAT(
|
|
CONVERT(nvarchar(30), SYSDATETIME(), 121), N' | COMPARE_DONE | ',
|
|
N'RowDiffTables=', @RowDiffCount,
|
|
N' | ColumnDiffs=', @ColDiffCount,
|
|
N' | ModuleDiffs=', @ModuleDiffCount,
|
|
N' | TriggerDiffs=', @TriggerDiffCount,
|
|
N' | PrincipalDiffs=', @PrincipalDiffCount,
|
|
N' | RoleMemberDiffs=', @RoleMemberDiffCount
|
|
), @sev, 1) WITH NOWAIT;
|