How can I compare the schema of two databases?

Posted on

Question :

Is there a way to find the differences in two SQL Server databases (schema only). One is local and the second is at a customer’s site. We are experiencing problems with crystal reports running some reports and some code not executing and it would appear that the schemas don’t match.

Can I run the same command on both databases and compare the results to tell where the differences are?

Answer :

If you cannot use one of the many tools out there because of connectivity problems and want an “offline” compare, you can use SSMS to generate scripts for all database objects by right clicking on the database and using the “Tasks…/Generate Scripts” function, and make sure you select to create one file per object.

When you have done that for both databases, get the two sets of scripts onto a local machine in two separate folders and use WinMerge (or similar) to compare the two.

After struggling with an easy way to do this same task – see what’s changed between 2 models, I wrote the following SQL Script that will compare two schemas to determine new and deleted columns

set nocount on;
-- Set the two variables newmodel and oldmodel to the appropriate database names and execute the script

declare @newmodel varchar(50), @oldmodel varchar(50);

Set @newmodel = '[NewModel to Compare]';
set @oldmodel = '[OldModel to Compare]';


Declare @Temp table (TABLE_SCHEMA varchar(40), TABLE_NAME varchar(40), COLUMN_NAME varchar(50), ORDINAL_POSITION int, IS_NULLABLE varchar(5), NullChange varchar(5), Comment varchar(50));

Declare @script varchar(5000);


set @script = '
Select nc.TABLE_SCHEMA, nc.TABLE_NAME, nc.COLUMN_NAME, nc.ORDINAL_POSITION, nc.IS_NULLABLE, IIF(nc.IS_NULLABLE <> oc.IS_NULLABLE, ''Yes'', ''No''), 
        IIF(oc.COLUMN_NAME IS NULL, convert(varchar(20), ''ADDED COLUMN''), convert(varchar(20), ''--'')) as Comment
    from {NEW}.INFORMATION_SCHEMA.COLUMNS nc
        LEFT join {OLD}.INFORMATION_SCHEMA.COLUMNS oc 
            on nc.TABLE_NAME = oc.TABLE_NAME and nc.COLUMN_NAME = oc.COLUMN_NAME
UNION ALL
    Select oc.TABLE_SCHEMA, oc.TABLE_NAME, oc.COLUMN_NAME, oc.ORDINAL_POSITION, oc.IS_NULLABLE, ''No'', ''DELETED COLUMN'' as Comment
    from {OLD}.INFORMATION_SCHEMA.COLUMNS oc
    where CONCAT(oc.TABLE_NAME, ''.'', oc.COLUMN_NAME) 
        not in (Select CONCAT(TABLE_NAME, ''.'', COLUMN_NAME) from {NEW}.INFORMATION_SCHEMA.COLUMNS)
';


Set @script = replace(@script, '{OLD}', @oldmodel);
Set @script = replace(@script, '{NEW}', @newmodel);

--print @script

Insert into @Temp
    exec(@script);

Select * from @Temp where Comment <> '--'
order by TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME;
go

Another option is to use SQL Server Data Tools (SSDT), an extension of Visual Studio. You can extract your database schema as a .dacpac file and compare that with another .dacpac file or an existing database. SSDT is included with SQL Server 2012 client tools, making it pretty accessible. You can find the full instructions of how to run the compare on the MSDN site.

Do a search for “SQL Server Compare” and you’ll find lots of tools. The one we use at my job is Red Gate SQLCompare. It has a 14 day trial. But since you are talking about two different environments I don’t think that would work for you, unless the client sends you a backup of their DB. The other option is to write queries against the system tables (like sys.indexes, sys.tables, etc).

Maybe this free script https://github.com/dlevsha/compalex can help you. It support Microsoft SQL Server.

Compalex is a free lightweight script to compare two database schemas. It
supports MySQL, MS SQL Server and PostgreSQL.

You can try demo here

http://demo.compalex.net/

If you need to compare more than one database file you could script SQLPackage.exe.

I don’t have working code for you but you could look at the SQLPackage.exe documentation for some inspiration.

You would extract your master database to a dacpac file and then compare the dacpac file to the rest of your databases. The result of the comparison could either be a xml report of the changes or a .sql file you can run to synchronize the databases.

Something like this:

sqlpackage.exe /a:Extract /scs:Server=MyLaptopSQL2014;Database=Test; /tf:C:UsersKevin3NFDocumentsSQLScriptsDACPACSTest.dacpac  

and then

sqlpackage.exe /a:Script /sf:C:UsersKevin3NFDocumentsSQLScriptsDACPACSTest.dacpac /tsn:MyLaptopSQL2014 /tdn:Test1 /op:C:UsersKevin3NFDocumentsSQLScriptsDACPACSDeltasTest1.sql /p:DropObjectsNotInSource=True /p:DropIndexesNotInSource=True 
 sqlpackage.exe /a:Script /sf:C:UsersKevin3NFDocumentsSQLScriptsDACPACSTest.dacpac /tsn:MyLaptopSQL2014 /tdn:Test2 /op:C:UsersKevin3NFDocumentsSQLScriptsDACPACSDeltasTest2.sql /p:DropObjectsNotInSource=True /p:DropIndexesNotInSource=True 

You can have a look at this article or this one for sample code.

Leave a Reply

Your email address will not be published. Required fields are marked *