/* ================== Ayarlar ================== */

DECLARE @SourceDB sysname = N'Kalite';            -- Kaynak DB (source)

DECLARE @TargetDB sysname = N'KaliteBlueWater';   -- Hedef DB (destination)

/* ============================================ */

IF OBJECT_ID('tempdb..#src') IS NOT NULL DROP TABLE #src;

IF OBJECT_ID('tempdb..#tgt') IS NOT NULL DROP TABLE #tgt;

CREATE TABLE #src(

  schema_name sysname,

  table_name  sysname,

  column_name sysname,

  data_type   sysname,

  max_length  int,

  precision   tinyint,

  scale       tinyint,

  is_nullable bit,

  is_identity bit

);

CREATE TABLE #tgt(

  schema_name sysname,

  table_name  sysname,

  column_name sysname,

  data_type   sysname,

  max_length  int,

  precision   tinyint,

  scale       tinyint,

  is_nullable bit,

  is_identity bit

);

DECLARE @sql nvarchar(max);

/* -------- Kaynak kolonlarını çek -------- */

SET @sql = N'

SELECT

  s.name AS schema_name,

  t.name AS table_name,

  c.name AS column_name,

  ty.name AS data_type,

  c.max_length,

  c.precision,

  c.scale,

  c.is_nullable,

  CAST(CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END AS bit) AS is_identity

FROM ' + QUOTENAME(@SourceDB) + N'.sys.tables t

JOIN ' + QUOTENAME(@SourceDB) + N'.sys.schemas s ON s.schema_id = t.schema_id

JOIN ' + QUOTENAME(@SourceDB) + N'.sys.columns c ON c.object_id = t.object_id

JOIN ' + QUOTENAME(@SourceDB) + N'.sys.types ty ON ty.user_type_id = c.user_type_id

LEFT JOIN ' + QUOTENAME(@SourceDB) + N'.sys.identity_columns ic ON ic.object_id=c.object_id AND ic.column_id=c.column_id

';

INSERT INTO #src EXEC sys.sp_executesql @sql;

/* -------- Hedef kolonlarını çek -------- */

SET @sql = N'

SELECT

  s.name AS schema_name,

  t.name AS table_name,

  c.name AS column_name,

  ty.name AS data_type,

  c.max_length,

  c.precision,

  c.scale,

  c.is_nullable,

  CAST(CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END AS bit) AS is_identity

FROM ' + QUOTENAME(@TargetDB) + N'.sys.tables t

JOIN ' + QUOTENAME(@TargetDB) + N'.sys.schemas s ON s.schema_id = t.schema_id

JOIN ' + QUOTENAME(@TargetDB) + N'.sys.columns c ON c.object_id = t.object_id

JOIN ' + QUOTENAME(@TargetDB) + N'.sys.types ty ON ty.user_type_id = c.user_type_id

LEFT JOIN ' + QUOTENAME(@TargetDB) + N'.sys.identity_columns ic ON ic.object_id=c.object_id AND ic.column_id=c.column_id

';

INSERT INTO #tgt EXEC sys.sp_executesql @sql;

/* -------- Karşılaştırma -------- */

WITH comp AS (

    SELECT

        @SourceDB AS source_db,

        @TargetDB AS target_db,

        COALESCE(s.schema_name, t.schema_name) AS schema_name,

        COALESCE(s.table_name,  t.table_name)  AS table_name,

        COALESCE(s.column_name, t.column_name) AS column_name,

        s.data_type   AS src_type,

        s.max_length  AS src_len,

        s.precision   AS src_prec,

        s.scale       AS src_scale,

        s.is_nullable AS src_null,

        s.is_identity AS src_ident,

        t.data_type   AS tgt_type,

        t.max_length  AS tgt_len,

        t.precision   AS tgt_prec,

        t.scale       AS tgt_scale,

        t.is_nullable AS tgt_null,

        t.is_identity AS tgt_ident

    FROM #src s

    FULL OUTER JOIN #tgt t

      ON t.schema_name = s.schema_name

     AND t.table_name  = s.table_name

     AND t.column_name = s.column_name

)

SELECT

    source_db,

    target_db,

    schema_name,

    table_name,

    column_name,

    src_type, src_len, src_prec, src_scale, src_null, src_ident,

    tgt_type, tgt_len, tgt_prec, tgt_scale, tgt_null, tgt_ident,

    -- typex: tam veri tipi tanımı (MAX desteği ile)

    CASE

        WHEN src_type IN ('varchar','char','varbinary','binary')

            THEN CONCAT(src_type, '(', CASE WHEN src_len = -1 THEN 'MAX' ELSE CONVERT(varchar(20), src_len) END, ')')

        WHEN src_type IN ('nvarchar','nchar')

            THEN CONCAT(src_type, '(', CASE WHEN src_len = -1 THEN 'MAX' ELSE CONVERT(varchar(20), src_len/2) END, ')')

        WHEN src_type IN ('decimal','numeric')

            THEN CONCAT(src_type, '(', CONVERT(varchar(10), src_prec), ',', CONVERT(varchar(10), src_scale), ')')

        WHEN src_type IN ('datetime2','time','datetimeoffset')

            THEN CONCAT(src_type, '(', CONVERT(varchar(10), src_scale), ')')

        ELSE src_type

    END AS typex,

    CASE

      WHEN tgt_type IS NULL THEN

        CONCAT(

          'ALTER TABLE ', QUOTENAME(@TargetDB), '.', QUOTENAME(schema_name), '.', QUOTENAME(table_name),

          ' ADD ', QUOTENAME(column_name), ' ',

          CASE

            WHEN src_type IN ('varchar','char','varbinary','binary')

              THEN CONCAT(src_type, '(', CASE WHEN src_len = -1 THEN 'MAX' ELSE CONVERT(varchar(20), src_len) END, ')')

            WHEN src_type IN ('nvarchar','nchar')

              THEN CONCAT(src_type, '(', CASE WHEN src_len = -1 THEN 'MAX' ELSE CONVERT(varchar(20), src_len/2) END, ')')

            WHEN src_type IN ('decimal','numeric')

              THEN CONCAT(src_type, '(', CONVERT(varchar(10), src_prec), ',', CONVERT(varchar(10), src_scale), ')')

            WHEN src_type IN ('datetime2','time','datetimeoffset')

              THEN CONCAT(src_type, '(', CONVERT(varchar(10), src_scale), ')')

            ELSE src_type

          END,

          CASE WHEN src_null = 1 THEN ' NULL' ELSE ' NOT NULL' END,

          ';'

        )

      WHEN src_type IS NOT NULL AND tgt_type IS NOT NULL AND (

             src_type <> tgt_type

          OR (src_type IN ('varchar','char','varbinary','binary') AND ISNULL(src_len,0) <> ISNULL(tgt_len,0))

          OR (src_type IN ('nvarchar','nchar') AND ISNULL(src_len,0) <> ISNULL(tgt_len,0))

          OR (src_type IN ('decimal','numeric') AND (ISNULL(src_prec,0) <> ISNULL(tgt_prec,0) OR ISNULL(src_scale,0) <> ISNULL(tgt_scale,0)))

          OR (src_type IN ('datetime2','time','datetimeoffset') AND ISNULL(src_scale,0) <> ISNULL(tgt_scale,0))

          OR ISNULL(src_null,0) <> ISNULL(tgt_null,0)

          OR ISNULL(src_ident,0) <> ISNULL(tgt_ident,0)

          )

      THEN

        CONCAT(

          'ALTER TABLE ', QUOTENAME(@TargetDB), '.', QUOTENAME(schema_name), '.', QUOTENAME(table_name),

          ' ALTER COLUMN ', QUOTENAME(column_name), ' ',

          CASE

            WHEN src_type IN ('varchar','char','varbinary','binary')

              THEN CONCAT(src_type, '(', CASE WHEN src_len = -1 THEN 'MAX' ELSE CONVERT(varchar(20), src_len) END, ')')

            WHEN src_type IN ('nvarchar','nchar')

              THEN CONCAT(src_type, '(', CASE WHEN src_len = -1 THEN 'MAX' ELSE CONVERT(varchar(20), src_len/2) END, ')')

            WHEN src_type IN ('decimal','numeric')

              THEN CONCAT(src_type, '(', CONVERT(varchar(10), src_prec), ',', CONVERT(varchar(10), src_scale), ')')

            WHEN src_type IN ('datetime2','time','datetimeoffset')

              THEN CONCAT(src_type, '(', CONVERT(varchar(10), src_scale), ')')

            ELSE src_type

          END,

          CASE WHEN src_null = 1 THEN ' NULL' ELSE ' NOT NULL' END,

          ';'

        )

      WHEN src_type IS NULL THEN

        CONCAT(

          'ALTER TABLE ', QUOTENAME(@TargetDB), '.', QUOTENAME(schema_name), '.', QUOTENAME(table_name),

          ' DROP COLUMN ', QUOTENAME(column_name), ';'

        )

    END AS alter_sql

FROM comp

WHERE

      src_type IS NULL

   OR tgt_type IS NULL

   OR src_type <> tgt_type

   OR (src_type IN ('varchar','char','varbinary','binary') AND ISNULL(src_len,0) <> ISNULL(tgt_len,0))

   OR (src_type IN ('nvarchar','nchar') AND ISNULL(src_len,0) <> ISNULL(tgt_len,0))

   OR (src_type IN ('decimal','numeric') AND (ISNULL(src_prec,0) <> ISNULL(tgt_prec,0) OR ISNULL(src_scale,0) <> ISNULL(tgt_scale,0)))

   OR (src_type IN ('datetime2','time','datetimeoffset') AND ISNULL(src_scale,0) <> ISNULL(tgt_scale,0))

   OR ISNULL(src_null,0) <> ISNULL(tgt_null,0)

   OR ISNULL(src_ident,0) <> ISNULL(tgt_ident,0)

ORDER BY schema_name, table_name, column_name;