Vergleich Struktur zweier Datenbanken / Compare structure of two databases

Das nachfolgende T-SQL-Script vergleicht die Metadaten/Strukturdaten von zwei Datenbanken.

The following T-SQL script allows compares the metadata/structore of two databases.

DECLARE @sDB1 VARCHAR(100) = 'OLDemoReweAbfD';
DECLARE @sDB2 VARCHAR(100) = 'OLReweAbf';
DECLARE @sTABLENMAME VARCHAR(100) = '%';
DECLARE @sCOLUMNMAME VARCHAR(100) = '%';
DECLARE @sSQL NVARCHAR(MAX);

SET @sSQL = '
SELECT ISNULL(Schema1.TableName, Schema2.TableName) AS TableOrViewName,
       ISNULL(Schema1.ColumnName, Schema2.ColumnName) AS ColumnName,
       Schema1.DBName,
       Schema1.SchemaName,
       Schema1.name DataType,
       Schema1.Length,
       Schema1.Precision,
       Schema1.Scale,
       Schema1.Is_Identity,
       Schema1.Is_Nullable,
       Schema2.DBName,
       Schema2.SchemaName,
       Schema2.name DataType,
       Schema2.Length,
       Schema2.Precision,
       Schema2.Scale,
       Schema2.Is_Identity,
       Schema2.Is_Nullable
FROM   
    (SELECT ''' + @sDB1 + ''' DbName,
           SCHEMA_NAME(schema_id) SchemaName,
           t.Name TableName,
           c.Name ColumnName,
           st.Name,
           c.Max_Length Length,
           c.Precision,
           c.Scale,
           c.Is_Identity,
           c.Is_Nullable
    FROM   ' + @sDB1 + '.sys.tables t
           INNER JOIN ' + @sDB1 + 
 '.sys.columns c ON t.Object_ID = c.Object_ID
           INNER JOIN systypes st ON St.xType = c.System_Type_id
    UNION SELECT ''' + @sDB1 + ''' DbName,
           SCHEMA_NAME(schema_id) SchemaName,
           t.Name TableName,
           c.Name ColumnName,
           st.Name,
           c.Max_Length Length,
           c.Precision,
           c.Scale,
           c.Is_Identity,
           c.Is_Nullable
    FROM   ' + @sDB1 + '.sys.views t
           INNER JOIN ' + @sDB1 + '.sys.columns c ON t.Object_ID = c.Object_ID
           INNER JOIN systypes st ON St.xType = c.System_Type_id
    ) Schema1 
    FULL OUTER JOIN
    (SELECT ''' + @sDB2 + ''' DbName,
           SCHEMA_NAME(schema_id) SchemaName,
           t.name TableName,
           c.name ColumnName,
           st.Name,
           c.max_length Length,
           c.Precision,
           c.Scale,
           c.Is_Identity,
           c.Is_Nullable
    FROM   ' + @sDB2 + '.sys.tables t
           INNER JOIN ' + @sDB2 + 
 '.sys.columns c ON t.Object_ID = c.Object_ID
           INNER JOIN systypes st ON St.xType = c.System_Type_id
    UNION SELECT ''' + @sDB2 + ''' DbName,
           SCHEMA_NAME(schema_id) SchemaName,
           t.Name TableName,
           c.Name ColumnName,
           st.Name,
           c.Max_Length Length,
           c.Precision,
           c.Scale,
           c.Is_Identity,
           c.Is_Nullable
    FROM   ' + @sDB2 + '.sys.views t
           INNER JOIN ' + @sDB2 + '.sys.columns c ON t.Object_ID = c.Object_ID
           INNER JOIN systypes st ON St.xType = c.System_Type_id    ) Schema2
    ON Schema1.TableName = Schema2.TableName
    AND Schema1.ColumnName = Schema2.ColumnName 
WHERE (ISNULL(Schema1.TableName, '''') LIKE ''' + @sTABLENMAME + ''' OR ISNULL(Schema2.TableName, '''') LIKE ''' + @sTABLENMAME + ''') 
AND (ISNULL(Schema1.ColumnName, '''') LIKE ''' + @sCOLUMNMAME + ''' OR ISNULL(Schema2.ColumnName, '''') LIKE ''' + @sCOLUMNMAME + 
 ''') 
AND ((ISNULL(Schema1.name, '''') <> ISNULL(Schema2.name, ''''))
  OR (ISNULL(Schema1.Length, 0) <> ISNULL(Schema2.Length, 0))
  OR (ISNULL(Schema1.Precision, 0) <> ISNULL(Schema2.Precision, 0))
  OR (ISNULL(Schema1.Scale, 0) <> ISNULL(Schema2.Scale, 0))
  OR (ISNULL(Schema1.Is_Identity, 0) <> ISNULL(Schema2.Is_Identity, 0))
  OR (ISNULL(Schema1.Is_Nullable, 0)<> ISNULL(Schema2.Is_Nullable, 0))
)
AND NOT (ISNULL(Schema1.name, '''') = ''sysname'' OR ISNULL(Schema2.name, '''') = ''sysname'')
AND NOT (ISNULL(Schema1.name, '''') = ''smallint'' AND ISNULL(Schema2.name, '''') = ''KHKBoolean'')
AND NOT (ISNULL(Schema1.name, '''') = ''KHKBoolean'' AND ISNULL(Schema2.name, '''') = ''smallint'')
ORDER BY ISNULL(Schema1.TableName, Schema2.TableName),
         ISNULL(Schema1.ColumnName, Schema2.ColumnName)
'

EXEC sp_executesql @sSQL