How can I compare the schema of two databases?

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 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 5

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).

Answer 6

The easiest way is to use an automated tool built for this purpose, but if you don't have access to one, you can get all of the basic information that you need from the INFORMATION_SCHEMA views.

Using the metadata in INFORMATION_SCHEMA is probably an easier option than generating DDL scripts and doing a source compare because you have much more control over how the data is presented. You can't really control the order in which generated scripts will present the objects in a database. Also, the scripts contain a bunch of text that may be implementation dependent by default and may cause a lot of mismatch "noise" when what you probably really need to focus on is a missing table, view or column, or possibly a column data type or size mismatch.

Write a query (or queries) to get the information that matters to your code from the INFORMATION_SCHEMA views and run it on each SQL Server from SSMS. You can then either dump the results to a file and use a text file compare tool (even MS Word) or you can dump the results to tables and run SQL queries to find mismatches.

Answer 7

I am including this answer for the sake of a new question that was marked as a duplicate.

I once had to compare two production databases and find any schema differences between them. The only items of interest were tables that had been added or dropped and columns that had been added, removed, or altered. I no longer have the SQL scripts I developed, but what follows is the general strategy. And the database was not SQL Server, but I think the same strategy applies.

First, I created what can best be described as a metadatabase. The user tables of this database contained data descriptions copied from the system tables of the production databases. Things like Table Name, Column Name, Data Type and Precision. There was one more item, Database Name, that did not exist in either of the production databases.

Next, I developed scripts that coupled selects from the system tables of the production databases with inserts into the user tables of the metadatabase.

Finally, I developed queries to find tables that existed in one database but not the other, and columns from tables in both database that were only in one database, and columns with inconsistent definitions between the two databases.

Out of about 100 tables and 600 columns, I found a handful of inconsistencies, and one column that was defined as a floating point in one database and an integer in the other. That last one turned out to be a godsend, because it unearthed a problem that had been plaguing one of the databases for years.

The model for the metadatabase was suggested by the system tables in question. The queries were not hard to construct, revolving mostly around group by and having count(database name) = 1.

In your case, with 700 production databases, you might want to automate the first two steps more than I did with just two databases to compare. But the idea is similar.

Answer 8

I had this exact same question and I believe that the Microsoft SQL Server Management Studio (SSMS) has a much easier/simpler solution than anything I saw here. I have a production site with MS SQL Server Express and soon to be several more where I don't want to have to install VisualStudio or other applications other than SSMS.

So within SSMS, right click on the database to get the schema for. Select Tasks > Generate Scripts... to open a wizard to script the schema and configuration for the entire database (or selected objects if you want). I kept all the default options except the path/filename, but the tool has a plethora of options. The wizard created one SQL which I copied via OneDrive back to my PC. I then used Notepad++ to compare the SQL to a file generated in the same way against my SIT database. You have to filter out hits from the date/time in comments, but otherwise it is a great comparison of the two databases.

Presto! Writing this up was significantly harder than doing the actual compare.

Answer 9

A great tool I use (although not updated in a while still works) is AdeptSqlDiff

Does both schema compares as well as data comparisons. Just like RedGate there is a cost but also a 30 day trial. And the price is pretty reasonable.

Answer 10

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/

Answer 11

There are many third party tools out there which will do schema and data compare, and synchronization. Two tools you can use are the ones my team and I have developed, xSQL Schema Compare for schema comparisons and xSQL Data Compare for data comparisons between objects with the same schema. Hope this helps!
Disclaimer: I'm affiliated to xSQL

Answer 12

There is a lot of tools on the market which you might use to get the job done. My company is using ApexSQL Diff for both comparison and sync because it is free for Azure, but you can’t go wrong with either Devart or Redgate tools.

Answer 13

I'm a fan of SQL DBDiff, which is an open source tool you can use to compare tables, views, functions, users, etc. of two instances of SQL Server databases and generate a change script between the source and destination databases.

Answer 14

I've made a MssqlMerge utility that allows to compare MSSQL databases, both structure and data. There is a Free version available that allows to compare table definitions, views, stored procedures and functions. And also there is a Pro version that supports more object types and have 'Query result diff' feature where you can run and compare any query results, including queries against system views, to compare some other details not availble out of the box.

Answer 15

Check this out:

SELECT TABLE_SCHEMA ,
       TABLE_NAME ,
       COLUMN_NAME ,
       ORDINAL_POSITION ,
       COLUMN_DEFAULT ,
       DATA_TYPE ,
       CHARACTER_MAXIMUM_LENGTH ,
       NUMERIC_PRECISION ,
       NUMERIC_PRECISION_RADIX ,
       NUMERIC_SCALE ,
       DATETIME_PRECISION
FROM   INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA in ('dbo','meta')
and table_name in (select name from sys.tables)
order by TABLE_SCHEMA ,       TABLE_NAME ,ORDINAL_POSITION

Answer 16

I use this free (and open source) tool: OpenDBDiff

Answer 17

DBDiff is the best tool for this, you can find it here.