Comparing Two Microsoft SQL Server Databases

One of the most common operations that people want to perform on SQL databases is to compare one database to another database, to see what’s changed since the second database was cloned from the first one. However, nobody ever seems to supply any built-in functionality to do that. So you always have to pay some jerk for some third-party app to compare them.

I admit it. I used to pay for it. Then one day, I realized that, hey, I’m some jerk…

This script compares the schemas of two databases running on (potentially) two different Microsoft SQL Server, uh, servers, and outputs a list of differences between them. In the first two lines, the names of the source and target databases should be substituted as appropriate. Note that if the databases are indeed located on two different servers, it may first be necessary to create a “linked server” using either the Management Studio tool or the sp_addlinkedserver stored procedure.

declare @Source varchar(max) = 'SourceServer.SourceDatabase.dbo.';
declare @Target varchar(max) = 'TargetServer.TargetDatabase.dbo.';

declare @DifferenceScript varchar(max) =
'
set nocount on

-- declare table structures

declare @TablesToMigrate as table
(
    TableName varchar(128),
    SourceTableID int,
    DifferenceTypeID int,
    TargetTableID int,
    RowNumber int,
    primary key(TableName)
);

declare @ColumnsToMigrate as table
(
    TableName varchar(128),
    ColumnName varchar(128),
    SourceColumnID int,
    SourceColumnTypeID int,
    SourceColumnLength int,
    SourceColumnIsNullable int,
    DifferenceTypeID int,
    TargetColumnID int,
    TargetColumnTypeID int,
    TargetColumnLength int,
    TargetColumnIsNullable int,
    RowNumber int,
    primary key(TableName, ColumnName)
);

declare @PrimaryKeysToMigrate as table
(
    PrimaryKeyName varchar(256),
    SourcePrimaryKeyID int,
    SourceParentTableID int,
    SourceParentTableName varchar(max),
    SourceColumnName varchar(max),
    DifferenceTypeID int,
    TargetPrimaryKeyID int,
    TargetParentTableID int,
    TargetParentTableName varchar(max),
    TargetColumnName varchar(max),
    primary key(PrimaryKeyName)
);

declare @ForeignKeysToMigrate as table
(
    ForeignKeyName varchar(256),
    SourceForeignKeyID int,
    SourceParentTableID int,
    SourceParentTableName varchar(max),
    SourceReferencedObjectID int,
    SourceReferencedObjectName varchar(max),
    DifferenceTypeID int,
    TargetForeignKeyID int,
    TargetParentTableID int,
    TargetParentTableName varchar(max),
    TargetReferencedObjectID int,
    TargetReferencedObjectName varchar(max),
    primary key(ForeignKeyName)
);

declare @StoredProceduresToMigrate as table
(
    StoredProcedureName varchar(256),
    SourceStoredProcedureID int,
    SourceText varchar(max),
    DifferenceTypeID int,
    TargetStoredProcedureID int,
    TargetText varchar(max),
    primary key(StoredProcedureName)
);

declare @DifferenceTypes as table
(
    DifferenceTypeID int,
    Name varchar(max)
)

-- difference types

declare @DifferenceTypeIDUnchanged int = 1;
declare @DifferenceTypeIDAdded int = 2;
declare @DifferenceTypeIDRemoved int = 3;
declare @DifferenceTypeIDModified int = 4;

insert into @DifferenceTypes
    select @DifferenceTypeIDUnchanged, ''Unchanged''
    union all select @DifferenceTypeIDAdded, ''Added''
    union all select @DifferenceTypeIDRemoved, ''Removed''
    union all select @DifferenceTypeIDModified, ''Modified''

-- 

declare @DataTypesWithLengths as table
(
    Name varchar(max)
);

insert into @DataTypesWithLengths
    select ''char''
    union all select ''nchar''
    union all select ''nvarchar''
    union all select ''varchar''

-- get tables from source database

insert into @TablesToMigrate
    select
        so.name,
        so.id,    -- SourceTableID
        null,    -- DifferenceTypeID
        null,    -- TargetTableID
        null    -- RowNumber
    from
        ' + @Source + 'sysobjects so
    where
        so.xtype = ''U''    

-- get columns from source database

insert into @ColumnsToMigrate
    select
        so.name,
        sc.name,
        sc.colid,        -- SourceColumnID
        sc.xtype,        -- SourceColumnType
        sc.[length],    -- SourceColumnLength
        sc.isnullable,    -- SourceColumnIsNullable
        null,            -- DifferenceTypeID
        null,            -- TargetColumnID
        null,            -- TargetColumnType
        null,            -- TargetColumnLength
        null,            -- TargetColumnIsNullable
        null            -- RowNumber
    from
        ' + @Source + 'sysobjects so
        join ' + @Source + 'syscolumns sc on sc.id = so.id
    where
        so.name in (select TableName from @TablesToMigrate)

-- get primary keys from source database

insert into @PrimaryKeysToMigrate
    select
        si.name,    -- PrimaryKeyName
        si.id,        -- SourcePrimaryKeyID
        so.id,        -- SourceParentTableID
        so.name,    -- SourceParentTableName
        sc.name,    -- SourceColumnName
        null,        -- DifferenceTypeID
        null,        -- TargetPrimaryKeyID
        null,        -- TargetParentTableID
        null,        -- TargetParentTableName
        null        -- TargetColumnName
    from
        ' + @Source + 'sysindexes si
        join ' + @Source + 'sysobjects so
            on si.id = so.id
        join ' + @Source + 'sysobjects so2
            on si.name = so2.name
            and so2.parent_obj = si.id
            and so2.xtype = ''PK''
        join ' + @Source + 'sysindexkeys sik
            on si.id = sik.id
            and si.indid = sik.indid
        join ' + @Source + 'syscolumns sc
            on sik.id = sc.id
            and sik.colid = sc.colid        

-- get foreign keys from source database

insert into @ForeignKeysToMigrate
select
    so_k.name,
    so_k.id,        -- SourceForeignKeyID,
    so_t.id,        -- SourceParentTableID
    so_t.name,        -- SourceParentTableName
    sc.colid,        -- SourceReferencedObjectID
    sc.name,        -- SourceReferencedObjectName
    null,            -- DifferenceTypeID
    null,            -- TargetForeignKeyID
    null,            -- TargetParentTableID
    null,            -- TargetParentTableName
    null,            -- TargetReferencedObjectID,
    null            -- TargetReferencedObjectName
from
    ' + @Source + 'sysforeignkeys sfk
    join ' + @Source + 'sysobjects so_k on so_k.id = sfk.constid
    join ' + @Source + 'sysobjects so_t on so_t.id = sfk.fkeyid
    join ' + @Source + 'syscolumns sc on sc.id = so_t.id and sc.colid = sfk.fkey

-- get stored procedures from source database

insert into @StoredProceduresToMigrate
select
    so.name,        -- StoredProcedureName
    so.id,        -- SourceStoredProcedureID
    sc.[text],    -- SourceText
    null,        -- DifferenceTypeID
    null,        -- TargetStoredProcedureID
    null        -- TargetText
from
    ' + @Source + 'sysobjects so
    join ' + @Source + 'syscomments sc on sc.id = so.id
where
    so.xtype = ''P''
    and sc.colid = 1

-- for long stored procedures, there may be more than one syscomments row

update @StoredProceduresToMigrate
set
    SourceText =
    (
        select sc.[text]
        from ' + @Source + 'syscomments sc
        where sc.id = SourceStoredProcedureID
        order by sc.colid
        for xml path('''')
    )

-- get target ids for tables present in both databases

update @TablesToMigrate
set
    TargetTableID = so.id
from
    ' + @Target + 'sysobjects so
    join @TablesToMigrate ttm
        on ttm.TableName = so.name

-- tables present in source but not in target

update @TablesToMigrate
set
    DifferenceTypeID = @DifferenceTypeIDRemoved
from
    @TablesToMigrate ttm
where
    ttm.TargetTableID is null

-- tables present in target but not in source

insert into @TablesToMigrate
    select
        so.name,
        null,                    -- TableIDSource
        @DifferenceTypeIDAdded,
        so.id,                    -- TableIDTarget
        null                    -- RowNumber
    from
        ' + @Target + '[sysobjects] so
    where
        so.xtype = ''U''
        and not exists
        (
            select ''x''
            from
                @TablesToMigrate ttm
            where
                ttm.TableName = so.name
        )

-- columns present in both databases        

update @ColumnsToMigrate
set
    TargetColumnID = sc.colid,
    TargetColumnTypeID = sc.xtype,
    TargetColumnLength = sc.[length],
    TargetColumnIsNullable = sc.isnullable
from
    ' + @Target + '[syscolumns] sc
    join ' + @Target + '[sysobjects] so on so.id = sc.id
    join @ColumnsToMigrate ctm
        on ctm.TableName = so.name
        and ctm.ColumnName = sc.name

-- check columns for differences

update @ColumnsToMigrate
set
    DifferenceTypeID = @DifferenceTypeIDModified
where
    SourceColumnTypeID != TargetColumnTypeID
    or SourceColumnLength != TargetColumnLength
    or SourceColumnIsNullable != TargetColumnIsNullable

-- columns present in source but not in target

update @ColumnsToMigrate
set
    DifferenceTypeID = @DifferenceTypeIDRemoved
from
    @ColumnsToMigrate ctm
where
    ctm.TargetColumnID is null    

-- columns present in target but not in source

insert into @ColumnsToMigrate
    select
        so.name,
        sc.name,
        null,                    -- SourceColumnID
        null,                    -- SourceColumnType
        null,                    -- SourceColumnLength
        null,                    -- SourceColumnIsNullable
        @DifferenceTypeIDAdded,
        sc.colid,                -- TargetColumnID
        sc.xtype,                -- TargetColumnType
        sc.[length],            -- TargetColumnLength
        sc.isnullable,            -- TargetColumnIsNullable
        null                    -- RowNumber
    from
        ' + @Target + '[sysobjects] so
        join ' + @Target + '[syscolumns] sc on sc.id = so.id
    where
        so.xtype = ''U''
        and not exists
        (
            select ''x''
            from @ColumnsToMigrate ctm
            where ctm.TableName = so.name
            and ctm.ColumnName = sc.name
        )

-- other columns are unchanged

update @ColumnsToMigrate
set
    DifferenceTypeID = @DifferenceTypeIDUnchanged
where
    DifferenceTypeID is null        

-- if the table is added or removed, we don''t need to track each column and key as well

delete @ColumnsToMigrate
from
    @ColumnsToMigrate ctm
where
    exists
    (
        select ''x''
        from @TablesToMigrate ttm
        where ttm.TableName = ctm.TableName
        and ttm.DifferenceTypeID in (@DifferenceTypeIDAdded, @DifferenceTypeIDRemoved)
    )

delete @PrimaryKeysToMigrate
from
    @PrimaryKeysToMigrate pktm
where
    exists
    (
        select ''x''
        from @TablesToMigrate ttm
        where ttm.TableName = pktm.SourceParentTableName
        and ttm.DifferenceTypeID in (@DifferenceTypeIDAdded, @DifferenceTypeIDRemoved)
    )    

delete @ForeignKeysToMigrate
from
    @ForeignKeysToMigrate fktm
where
    exists
    (
        select ''x''
        from @TablesToMigrate ttm
        where ttm.TableName = fktm.SourceParentTableName
        and ttm.DifferenceTypeID in (@DifferenceTypeIDAdded, @DifferenceTypeIDRemoved)
    )        

-- primary keys present in both databases

update @PrimaryKeysToMigrate
set
    TargetPrimaryKeyID = si.id,
    TargetParentTableID = so.id,
    TargetParentTableName = so.name,
    TargetColumnName = sc.name
from
    @PrimaryKeysToMigrate pktm
    join ' + @Target + 'sysindexes si on si.name = pktm.PrimaryKeyName
    join ' + @Target + 'sysobjects so
        on si.id = so.id
    join ' + @Target + 'sysobjects so2
        on si.name = so2.name
        and so2.parent_obj = si.id
        and so2.xtype = ''PK''
    join ' + @Target + 'sysindexkeys sik
        on si.id = sik.id
        and si.indid = sik.indid
    join ' + @Target + 'syscolumns sc
        on sik.id = sc.id
        and sik.colid = sc.colid    

-- primary keys present in source but not in target

update @PrimaryKeysToMigrate
set DifferenceTypeID = @DifferenceTypeIDRemoved
where TargetPrimaryKeyID is null

-- primary keys present in target but not in source

insert into @PrimaryKeysToMigrate
    select
        si.name,                    -- PrimaryKeyName
        null,
        null,                        -- SourceParentTableID
        null,                        -- SourceParentTableName
        null,                        -- SourceColumnName
        @DifferenceTypeIDAdded,
        si.id,                        -- TargetPrimaryKeyID
        so.id,                        -- TargetParentTableID
        so.name,                    -- TargetParentTableName
        sc.name                        -- TargetColumnName
    from
        ' + @Target + 'sysindexes si
        join ' + @Target + 'sysobjects so
            on si.id = so.id
        join ' + @Target + 'sysobjects so2
            on si.name = so2.name
            and so2.parent_obj = si.id
            and so2.xtype = ''PK''
        join ' + @Target + 'sysindexkeys sik
            on si.id = sik.id
            and si.indid = sik.indid
        join ' + @Target + 'syscolumns sc
            on sik.id = sc.id
            and sik.colid = sc.colid
    where
        not exists
        (
            select ''x''
            from @PrimaryKeysToMigrate pktm
            where pktm.PrimaryKeyName = si.name
        )

-- other primary keys are unchanged

update @PrimaryKeysToMigrate
set DifferenceTypeID = @DifferenceTypeIDUnchanged
where DifferenceTypeID is null

-- foreign keys present in both databases

update @ForeignKeysToMigrate
set
    TargetForeignKeyID = so_k.id,
    TargetParentTableID    = so_t.id,
    TargetParentTableName = so_t.name,
    TargetReferencedObjectID = sc.colid,
    TargetReferencedObjectName = sc.name
from
    @ForeignKeysToMigrate fktm
    join ' + @Target + 'sysobjects so_k on so_k.name = fktm.ForeignKeyName
    join ' + @Target + 'sysforeignkeys sfk on sfk.constid = so_k.id
    join ' + @Target + 'sysobjects so_t on so_t.id = sfk.fkeyid
    join ' + @Target + 'syscolumns sc on sc.id = so_t.id and sc.colid = sfk.fkey

-- foreign keys present in source but not in target

update @ForeignKeysToMigrate
set
    DifferenceTypeID = @DifferenceTypeIDRemoved
from
    @ForeignKeysToMigrate fktm
where
    fktm.TargetForeignKeyID is null

-- foreign keys present in target but not in source

insert into @ForeignKeysToMigrate
    select
        so_k.name,
        null,                        -- SourceForeignKeyID
        null,                        -- SourceParentTableID
        null,                        -- SourceParentTableName
        null,                        -- SourceReferencedObjectID
        null,                        -- SourceReferencedObjectName
        @DifferenceTypeIDAdded,
        so_k.id,                    -- TargetForeignKeyID,
        so_t.id,                    -- TargetParentTableID
        so_t.name,                    -- TargetParentTableName
        sc.colid,                    -- TargetReferencedObjectID
        sc.name                        -- TargetReferencedObjectName
    from
        ' + @Target + 'sysforeignkeys sfk
        join ' + @Target + 'sysobjects so_k on so_k.id = sfk.constid
        join ' + @Target + 'sysobjects so_t on so_t.id = sfk.fkeyid
        join ' + @Target + 'syscolumns sc on sc.id = so_t.id and sc.colid = sfk.fkey
    where
        not exists
        (
            select ''x''
            from
                @ForeignKeysToMigrate fktm
            where
                fktm.ForeignKeyName = so_k.name
        )        

-- other foreign keys are unchanged

update @ForeignKeysToMigrate
set
    DifferenceTypeID = @DifferenceTypeIDUnchanged
where
    DifferenceTypeID is null    

-- if any child objects on a table have changed, then the table is "modified".

update @TablesToMigrate
set
    DifferenceTypeID = @DifferenceTypeIDModified
from
    @TablesToMigrate ttm
where
    DifferenceTypeID is null
    and exists
    (
        select ''x''
        from @ColumnsToMigrate ctm
        where
            ctm.TableName = ttm.TableName
            and ctm.DifferenceTypeID != @DifferenceTypeIDUnchanged
    )
    or exists
    (
        select ''x''
        from @PrimaryKeysToMigrate pktm
        where
            pktm.SourceParentTableName = ttm.TableName
            and pktm.DifferenceTypeID != @DifferenceTypeIDUnchanged
    )
    or exists
    (
        select ''x''
        from @ForeignKeysToMigrate fktm
        where
            fktm.SourceParentTableName = ttm.TableName
            and fktm.DifferenceTypeID != @DifferenceTypeIDUnchanged
    )    

-- the remainder of the tables are unchanged

update @TablesToMigrate
set DifferenceTypeID = @DifferenceTypeIDUnchanged
where DifferenceTypeID is null    

-- stored procedures present in both databases

update @StoredProceduresToMigrate
set
    TargetStoredProcedureID = sc.id,
    TargetText                = sc.text
from
    @StoredProceduresToMigrate sptm
    join ' + @Target + 'sysobjects so on so.name = sptm.StoredProcedureName
    join ' + @Target + 'syscomments sc on sc.id = so.id
where
    so.xtype = ''P''
    and sc.colid = 1

-- stored procedures present in source but not in target

update @StoredProceduresToMigrate
set
    DifferenceTypeID = @DifferenceTypeIDRemoved
where
    TargetStoredProcedureID is null

-- stored procedures present in target but not in source

insert into @StoredProceduresToMigrate
select
    so.name,                -- StoredProcedureName
    null,                    -- SourceStoredProcedureID
    null,                    -- SourceText
    @DifferenceTypeIDAdded,    -- DifferenceTypeID
    so.id,                    -- TargetStoredProcedureID
    sc.[text]                -- TargetText
from
    ' + @Target + 'sysobjects so
    join ' + @Target + 'syscomments sc on sc.id = so.id
where
    so.xtype = ''P''
    and not exists
    (
        select ''x''
        from @StoredProceduresToMigrate sptm
        where sptm.StoredProcedureName = so.name
    )

-- for long stored procedures, there may be more than one syscomments row

update @StoredProceduresToMigrate
set
    SourceText =
    (
        select sc.[text]
        from ' + @Target + 'syscomments sc
        where sc.id = SourceStoredProcedureID
        order by sc.colid
        for xml path('''')
    )

-- if the stored procedure text has changed, mark as modified

update @StoredProceduresToMigrate
set
    DifferenceTypeID = @DifferenceTypeIDModified
where
    SourceText != TargetText

-- other stored procedures are unchanged

update @StoredProceduresToMigrate
set DifferenceTypeID = @DifferenceTypeIDUnchanged
where DifferenceTypeID is null

-- output the results

select
    ttm.TableName,
    dt.Name as DifferenceTypeName
from
    @TablesToMigrate ttm
    left join @DifferenceTypes dt on dt.DifferenceTypeID = ttm.DifferenceTypeID
where
    ttm.DifferenceTypeID != @DifferenceTypeIDUnchanged

select
    ctm.TableName,
    ctm.ColumnName,
    dt.Name as DifferenceTypeName
from
    @ColumnsToMigrate ctm
    left join @DifferenceTypes dt on dt.DifferenceTypeID = ctm.DifferenceTypeID
where
    ctm.DifferenceTypeID != @DifferenceTypeIDUnchanged

select
    pktm.PrimaryKeyName,
    dt.Name as DifferenceTypeName
from
    @PrimaryKeysToMigrate pktm
    left join @DifferenceTypes dt on dt.DifferenceTypeID = pktm.DifferenceTypeID
where
    pktm.DifferenceTypeID != @DifferenceTypeIDUnchanged

select
    fktm.ForeignKeyName,
    fktm.SourceParentTableName,
    fktm.SourceReferencedObjectName,
    dt.Name as DifferenceTypeName,
    fktm.TargetParentTableName,
    fktm.TargetReferencedObjectName
from
    @ForeignKeysToMigrate fktm
    left join @DifferenceTypes dt on dt.DifferenceTypeID = fktm.DifferenceTypeID
where
    fktm.DifferenceTypeID != @DifferenceTypeIDUnchanged

select
    sptm.StoredProcedureName,
    sptm.SourceText,
    dt.name as DifferenceTypeName,
    sptm.TargetText
from
    @StoredProceduresToMigrate sptm
    left join @DifferenceTypes dt on dt.DifferenceTypeID = sptm.DifferenceTypeID
where
    sptm.DifferenceTypeID != @DifferenceTypeIDUnchanged
'

exec (@DifferenceScript)
Advertisements
This entry was posted in Uncategorized and tagged , , , , . Bookmark the permalink.

3 Responses to Comparing Two Microsoft SQL Server Databases

  1. Alan says:

    — other primary keys are unchanged
    — other stored procedures are unchanged
    Looks to me like these comments might mislead a bit. You appear to have checked for tables and columns with the same name, but some attributes moodified, but it looks to me like for primary keys and foreign keys the only check is that they have the same name on source and target – e.g. the columns in the key could change, but if the name stayed the same the script would not pick it up.

    I could test this, because otherwise your script is pretty much what I am looking for to address my current problem 🙂 Thanks for sharing

    • You’re right, nice catch. I guess I let that slip by me because I wasn’t really worried about accidentally modifying the fields referenced by primary and foreign keys, because that seems like something I would be pretty unlikely to do accidentally. As opposed to forgetting to migrate the keys entirely, which I do all the durn time.

  2. Chris says:

    This script saved me hours and hours of work. Thank you, above all else, for writing the variable names in a clear, readable format! Allowed me to make any necessary tweaks without beating my head into the keyboard.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s