Compare Database Script

Copy and paste to following script into a new query in SSMS. Change @DB1 and @DB2 to the names of the two databases to compare. Then execute.

Each row in the results will be the properties of each database column. The results will include a Status column that shows New, Deleted, Changed, or UnChanged/Identical. Following will be the Database name and the properties for that column, until you reach the second Database name and the properties of it’s columns.

USE master
GO

DECLARE
    @Server1 VARCHAR(100) = '', -- don't forget to include a dot at the end. Leave empty if on the same server.
    @Server2 VARCHAR(100) = '',-- don't forget to include a dot at the end. Leave empty if on the same server.
    @DB1 VARCHAR(100) = 'xspoc', --'SourceDatabaseName',
    @DB2 VARCHAR(100) = 'callon_xspoc' --'DestinationDatabaseName'

DECLARE @SQL NVARCHAR(MAX);

SET @SQL = '
SELECT
    CASE
        WHEN s1.[Column] IS NOT NULL
            AND s2.[Column] IS NULL
            THEN ''New''
        WHEN s1.[Column] IS NULL
            AND s2.[Column] IS NOT NULL
            THEN ''Deleted''
        WHEN s1.[Column] IS NOT NULL
            AND s2.[Column] IS NOT NULL
            AND (s1.[Type] <> s2.[Type]
                OR s1.[Length] <> s2.[Length]
                OR s1.[Precision] <> s2.[Precision]
                OR s1.Scale <> s2.Scale
                OR s1.IsNullable <> s2.IsNullable
                OR s1.IsIdentity <> s2.IsIdentity
                OR s1.IdentitySeed <> s2.IdentitySeed
                OR s1.IdentityIncrement <> s2.IdentityIncrement
                OR s1.DefaultValue <> s2.DefaultValue)
            THEN ''Changed''
        ELSE ''UnChanged''
    END [Status],
    s1.[Database],
    s1.[Schema],
    s1.[Table],
    s1.[Column],
    s1.[Type],
    s1.IsCharType,
    s1.[Length],
    s1.[Precision],
    s1.Scale,
    s1.IsNullable,
    s1.IsIdentity,
    s1.IdentitySeed,
    s1.IdentityIncrement,
    s1.DefaultValue,
    s1.[Order],
    s2.[Database],
    s2.[Schema],
    s2.[Table],
    s2.[Column],
    s2.[Type],
    s2.IsCharType,
    s2.[Length],
    s2.[Precision],
    s2.Scale,
    s2.IsNullable,
    s2.IsIdentity,
    s2.IdentitySeed,
    s2.IdentityIncrement,
    s2.DefaultValue,
    s2.[Order]
FROM (
    SELECT
        ''' + @DB1 + ''' AS [Database],
        s.name AS [Schema],
        t.name AS [Table],
        c.name AS [Column],
        tp.name AS [Type],
        CASE 
            WHEN tp.collation_name IS NOT NULL
                THEN 1
            ELSE 0
        END AS IsCharType,
        CASE
            WHEN c.max_length = -1
                THEN ''MAX''
            ELSE CAST(c.max_length AS VARCHAR(4))
        END AS [Length],
        c.[precision],
        c.scale,
        c.is_nullable AS IsNullable,
        c.is_identity AS IsIdentity,
        CAST(ISNULL(ic.seed_value, 0) AS INT) AS IdentitySeed,
        CAST(ISNULL(ic.increment_value, 0) AS INT) AS IdentityIncrement,
        dc.definition AS DefaultValue,
        c.column_id AS [Order]
    FROM ' + @Server1 + @DB1 + '.sys.tables t
        INNER JOIN ' + @Server1 + @DB1 + '.sys.schemas s ON s.schema_id = t.schema_id
        INNER JOIN ' + @Server1 + @DB1 + '.sys.columns c ON c.object_id = t.object_id
        INNER JOIN ' + @Server1 + @DB1 + '.sys.types tp ON tp.system_type_id = c.system_type_id
        LEFT OUTER JOIN ' + @Server1 + @DB1 + '.sys.identity_columns ic ON ic.object_id = t.object_id AND ic.name = c.name
        LEFT OUTER JOIN ' + @Server1 + @DB1 + '.sys.default_constraints dc ON dc.object_id = c.default_object_id
    ) s1
FULL OUTER JOIN (
    SELECT
        ''' + @DB2 + ''' AS [Database],
        s.name AS [Schema],
        t.name AS [Table],
        c.name AS [Column],
        tp.name AS [Type],
        CASE 
            WHEN tp.collation_name IS NOT NULL
                THEN 1
            ELSE 0
        END AS IsCharType,
        CASE
            WHEN c.max_length = -1
                THEN ''MAX''
            ELSE CAST(c.max_length AS VARCHAR(4))
        END AS [Length],
        c.[precision],
        c.scale,
        c.is_nullable AS IsNullable,
        c.is_identity AS IsIdentity,
        CAST(ISNULL(ic.seed_value, 0) AS INT) AS IdentitySeed,
        CAST(ISNULL(ic.increment_value, 0) AS INT) AS IdentityIncrement,
        dc.definition AS DefaultValue,
        c.column_id AS [Order]
    FROM ' + @Server2 + @DB2 + '.sys.tables t
        INNER JOIN ' + @Server2 + @DB2 + '.sys.schemas s ON s.schema_id = t.schema_id
        INNER JOIN ' + @Server2 + @DB2 + '.sys.columns c ON c.object_id = t.object_id
        INNER JOIN ' + @Server2 + @DB2 + '.sys.types tp ON tp.system_type_id = c.system_type_id
        LEFT OUTER JOIN ' + @Server2 + @DB2 + '.sys.identity_columns ic ON ic.object_id = t.object_id AND ic.name = c.name
        LEFT OUTER JOIN ' + @Server2 + @DB2 + '.sys.default_constraints dc ON dc.object_id = c.default_object_id
    ) s2
    ON s2.[Schema] = s1.[Schema]
    AND s2.[Table] = s1.[Table]
    AND s2.[Column] = s1.[Column]
ORDER BY status,
    CASE WHEN s1.[Database] IS NULL THEN s2.[Database] ELSE s1.[Database] END,
    CASE WHEN s1.[Schema] IS NULL THEN s2.[Schema] ELSE s1.[Schema] END,
    CASE WHEN s1.[Table] IS NULL THEN s2.[Table] ELSE s1.[Table] END,
    CASE WHEN s1.[Order] IS NULL THEN s2.[Order] ELSE s1.[Order] END
'

EXEC sp_executesql @SQL