/* ============================================================
   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;