Script: Comparar estructura de dos tablas Sql Server

En esta oportunidad comparto unos querys que pueden ayudarte a comparar dos tablas y identificar que campos existen en una u otra tabla.  Ademas agrego otros querys que genera la creación y eliminación de columnas.

Solo debe de poner en @Table1 y @Table2 los nombres de las tablas que desea comprar y/o generar scripts.

Query no.1 - Comparar estructura

Este query compara dos tablas y muestra que campos no están en la tabla1 o tabla2.

DECLARE @Table1 VARCHAR(100)='Tabla1'
        ,@Table2 VARCHAR(100)='Tabla2'

SELECT *
FROM (
       SELECT TABLE_NAME TABLE1, 
       COLUMN_NAME COLUMN1,
   CASE WHEN DATA_TYPE='VARCHAR' THEN 'VARCHAR('+Convert(VARCHAR(10),CHARacter_maximum_length)+')'
        WHEN DATA_TYPE='NVARCHAR' THEN 'NVARCHAR('+Convert(VARCHAR(10),CHARacter_maximum_length)+')'
    WHEN DATA_TYPE='VARBINARY' THEN 'VARBINARY('+Convert(VARCHAR(10),CHARacter_maximum_length)+')'
    WHEN DATA_TYPE='CHAR' THEN 'CHAR('+Convert(VARCHAR(10),CHARacter_maximum_length)+')'
    WHEN DATA_TYPE='DECIMAL' THEN 'DECIMAL('+Convert(VARCHAR(10),NUMERIC_Precision_Radix)+','+Convert(VARCHAR(10),NUMERIC_Scale)+')'
    WHEN DATA_TYPE='NUMERIC' THEN 'DECIMAL('+Convert(VARCHAR(10),NUMERIC_Precision_Radix)+','+Convert(VARCHAR(10),NUMERIC_Scale)+')'
      ELSE UPPER(DATA_TYPE)
   END DATA_TYPE1,
   CASE WHEN IS_NULLABLE='NO' THEN 'NOT NULL'
        ELSE 'NULL' 
   END IS_NULLABLE1
    FROM INFORMATION_SCHEMA.COLUMNS C
WHERE Table_Name=@Table1
) T1
LEFT OUTER JOIN
(
       SELECT TABLE_NAME TABLE2, 
       COLUMN_NAME COLUMN2,
   CASE WHEN DATA_TYPE='VARCHAR' THEN 'VARCHAR('+Convert(VARCHAR(10),CHARacter_maximum_length)+')'
        WHEN DATA_TYPE='NVARCHAR' THEN 'NVARCHAR('+Convert(VARCHAR(10),CHARacter_maximum_length)+')'
    WHEN DATA_TYPE='VARBINARY' THEN 'VARBINARY('+Convert(VARCHAR(10),CHARacter_maximum_length)+')'
    WHEN DATA_TYPE='CHAR' THEN 'CHAR('+Convert(VARCHAR(10),CHARacter_maximum_length)+')'
    WHEN DATA_TYPE='DECIMAL' THEN 'DECIMAL('+Convert(VARCHAR(10),NUMERIC_Precision_Radix)+','+Convert(VARCHAR(10),NUMERIC_Scale)+')'
    WHEN DATA_TYPE='NUMERIC' THEN 'DECIMAL('+Convert(VARCHAR(10),NUMERIC_Precision_Radix)+','+Convert(VARCHAR(10),NUMERIC_Scale)+')'
      ELSE DATA_TYPE
   END DATA_TYPE2,
   CASE WHEN IS_NULLABLE='NO' THEN 'NOT NULL'
        ELSE 'NULL' 
   END IS_NULLABLE2
    FROM INFORMATION_SCHEMA.COLUMNS C
WHERE Table_Name=@Table2
) T2
ON T1.COLUMN1 = T2.COLUMN2
WHERE ( T1.COLUMN1 IS NULL 
    OR T2.COLUMN2 IS NULL)

Query no.2 - Generar Script de creación de nuevos campos

Este query genera la creación de los nuevos campos, que existen en la búsqueda de la tabla1.

DECLARE @Table1 VARCHAR(100)='Tabla1'
        ,@Table2 VARCHAR(100)='Tabla2'

SELECT 'ALTER TABLE '+@Table2+' ADD '+COLUMN1 +' '+DATA_TYPE1+' ' +IS_NULLABLE1+
       CHAR(10)+CHAR(13)+'GO'
FROM (
       SELECT TABLE_NAME TABLE1, 
       COLUMN_NAME COLUMN1,
   CASE WHEN DATA_TYPE='VARCHAR' THEN 'VARCHAR('+Convert(VARCHAR(10),CHARacter_maximum_length)+')'
        WHEN DATA_TYPE='NVARCHAR' THEN 'NVARCHAR('+Convert(VARCHAR(10),CHARacter_maximum_length)+')'
    WHEN DATA_TYPE='VARBINARY' THEN 'VARBINARY('+Convert(VARCHAR(10),CHARacter_maximum_length)+')'
    WHEN DATA_TYPE='CHAR' THEN 'CHAR('+Convert(VARCHAR(10),CHARacter_maximum_length)+')'
    WHEN DATA_TYPE='DECIMAL' THEN 'DECIMAL('+Convert(VARCHAR(10),NUMERIC_Precision_Radix)+','+Convert(VARCHAR(10),NUMERIC_Scale)+')'
    WHEN DATA_TYPE='NUMERIC' THEN 'DECIMAL('+Convert(VARCHAR(10),NUMERIC_Precision_Radix)+','+Convert(VARCHAR(10),NUMERIC_Scale)+')'
      ELSE UPPER(DATA_TYPE)
   END DATA_TYPE1,
   CASE WHEN IS_NULLABLE='NO' THEN 'NOT NULL'
        ELSE 'NULL' 
   END IS_NULLABLE1
    FROM INFORMATION_SCHEMA.COLUMNS C
WHERE Table_Name=@Table1
) T1
LEFT OUTER JOIN
(
       SELECT TABLE_NAME TABLE2, 
       COLUMN_NAME COLUMN2,
   CASE WHEN DATA_TYPE='VARCHAR' THEN 'VARCHAR('+Convert(VARCHAR(10),CHARacter_maximum_length)+')'
        WHEN DATA_TYPE='NVARCHAR' THEN 'NVARCHAR('+Convert(VARCHAR(10),CHARacter_maximum_length)+')'
    WHEN DATA_TYPE='VARBINARY' THEN 'VARBINARY('+Convert(VARCHAR(10),CHARacter_maximum_length)+')'
    WHEN DATA_TYPE='CHAR' THEN 'CHAR('+Convert(VARCHAR(10),CHARacter_maximum_length)+')'
    WHEN DATA_TYPE='DECIMAL' THEN 'DECIMAL('+Convert(VARCHAR(10),NUMERIC_Precision_Radix)+','+Convert(VARCHAR(10),NUMERIC_Scale)+')'
    WHEN DATA_TYPE='NUMERIC' THEN 'DECIMAL('+Convert(VARCHAR(10),NUMERIC_Precision_Radix)+','+Convert(VARCHAR(10),NUMERIC_Scale)+')'
      ELSE UPPER(DATA_TYPE)
   END DATA_TYPE2,
   CASE WHEN IS_NULLABLE='NO' THEN 'NOT NULL'
        ELSE 'NULL' 
   END IS_NULLABLE2
    FROM INFORMATION_SCHEMA.COLUMNS C
WHERE Table_Name=@Table2
) T2
ON T1.COLUMN1 = T2.COLUMN2
WHERE ( T2.COLUMN2 IS NULL)

Query no.3 - Generar Script de eliminacion de campos

Este query genera la eliminación de los nuevos campos, que existen en la búsqueda de la tabla1.  La idea es, si se desea hacer rollback de los cambios implementados con el script anterior.

DECLARE @Table1 VARCHAR(100)='Tabla1'
        ,@Table2 VARCHAR(100)='Tabla2'

SELECT 'ALTER TABLE '+@Table2+' DROP COLUMN '+COLUMN1+
       CHAR(10)+CHAR(13)+'GO'
FROM (
       SELECT TABLE_NAME TABLE1, 
       COLUMN_NAME COLUMN1,
   CASE WHEN DATA_TYPE='VARCHAR' THEN 'VARCHAR('+Convert(VARCHAR(10),CHARacter_maximum_length)+')'
        WHEN DATA_TYPE='NVARCHAR' THEN 'NVARCHAR('+Convert(VARCHAR(10),CHARacter_maximum_length)+')'
    WHEN DATA_TYPE='VARBINARY' THEN 'VARBINARY('+Convert(VARCHAR(10),CHARacter_maximum_length)+')'
    WHEN DATA_TYPE='CHAR' THEN 'CHAR('+Convert(VARCHAR(10),CHARacter_maximum_length)+')'
    WHEN DATA_TYPE='DECIMAL' THEN 'DECIMAL('+Convert(VARCHAR(10),NUMERIC_Precision_Radix)+','+Convert(VARCHAR(10),NUMERIC_Scale)+')'
    WHEN DATA_TYPE='NUMERIC' THEN 'DECIMAL('+Convert(VARCHAR(10),NUMERIC_Precision_Radix)+','+Convert(VARCHAR(10),NUMERIC_Scale)+')'
      ELSE UPPER(DATA_TYPE)
   END DATA_TYPE1,
   CASE WHEN IS_NULLABLE='NO' THEN 'NOT NULL'
        ELSE 'NULL' 
   END IS_NULLABLE1
    FROM INFORMATION_SCHEMA.COLUMNS C
WHERE Table_Name=@Table1
) T1
LEFT OUTER JOIN
(
       SELECT TABLE_NAME TABLE2, 
       COLUMN_NAME COLUMN2,
   CASE WHEN DATA_TYPE='VARCHAR' THEN 'VARCHAR('+Convert(VARCHAR(10),CHARacter_maximum_length)+')'
        WHEN DATA_TYPE='NVARCHAR' THEN 'NVARCHAR('+Convert(VARCHAR(10),CHARacter_maximum_length)+')'
    WHEN DATA_TYPE='VARBINARY' THEN 'VARBINARY('+Convert(VARCHAR(10),CHARacter_maximum_length)+')'
    WHEN DATA_TYPE='CHAR' THEN 'CHAR('+Convert(VARCHAR(10),CHARacter_maximum_length)+')'
    WHEN DATA_TYPE='DECIMAL' THEN 'DECIMAL('+Convert(VARCHAR(10),NUMERIC_Precision_Radix)+','+Convert(VARCHAR(10),NUMERIC_Scale)+')'
    WHEN DATA_TYPE='NUMERIC' THEN 'DECIMAL('+Convert(VARCHAR(10),NUMERIC_Precision_Radix)+','+Convert(VARCHAR(10),NUMERIC_Scale)+')'
      ELSE UPPER(DATA_TYPE)
   END DATA_TYPE2,
   CASE WHEN IS_NULLABLE='NO' THEN 'NOT NULL'
        ELSE 'NULL' 
   END IS_NULLABLE2
    FROM INFORMATION_SCHEMA.COLUMNS C
WHERE Table_Name=@Table2
) T2
ON T1.COLUMN1 = T2.COLUMN2
WHERE ( T2.COLUMN2 IS NULL)