26

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?

Improve this question
1

18 Answers 18

Active Oldest Votes
17

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.

Improve this answer
11

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
Improve this answer
2
  • For a quick and dirty solution that doesn't require any extra software, this is great! It's exactly what I needed. Thanks! – Mir Oct 4 '17 at 20:29
  • Worked like a charm. The only thing we needed to change were old model and new model names. No other change after about 40 months this was answered! – Ozair Kafray Feb 8 at 10:54
8

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.

Improve this answer
1
  • Another benefit of this is you will have the option to Generate Scripts. This will create all the Delete/Alter/Create Scripts for you, making for an easier deployment by handing them off to the DBA. You could just click the Update Button to have it do it for you, however be EXTRA careful because the default settings may generate way more scripts than you would normally want to run (even when you uncheck all the differences you found)! I would ALWAYS avoid blindly clicking that Update button. Be careful you never accidentally click it as it is right next to the Generate Scripts. – MikeTeeVee Jun 1 '20 at 2:37
2

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.

Improve this answer
2

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.

Improve this answer
1

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

Improve this answer
1
  • SQL Compare works fine if you have logins to both servers. You can use a different login to each DB, so the client would have to ensure you had access. – Mark Sinkinson Mar 14 '15 at 7:47
1

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/

Improve this answer
1

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.

Improve this answer
1

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.

Improve this answer
0

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.

Improve this answer
0

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

Improve this answer
0

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.

Improve this answer
0

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.

Improve this answer
0

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.

Improve this answer
0

SQL Server Data Tool in Visual Studio is best to compare 2 sql server database.

To Compare database, in Visual Studio, you can navigate to "Tools"->"SQL Server"->"New Schema Project"

In the next screen, you need to select "Source" and "Destination" schema, which we want to compare. ( You can compare local and remote database also)

Once you add data sources (Source/destination), click "Compare" button and you will see difference.

There are more tools, but it is easy and free to use.

Source: Tools to Compare Two SQL Server Database

Improve this answer
1
  • 1
    Hi Vikas, it looks like Visual Studio was already mentioned as an answer here. Also, please make sure to disclose your affiliation when posting links like this one, that you wrote the page for. – LowlyDBA - John M Feb 13 at 16:04
-1

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

Improve this answer
-1

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

Improve this answer
1
  • Welcome to Database Administrators! As you see, all well-received answers here are more than just a link. Please consider editing your answer and add more information about the software, especially how it meets the requirements in the question. – Glorfindel May 30 '19 at 8:59
-2

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

enter image description here

Improve this answer
2
  • 3
    This really needs to be about comparing, not just getting the schema – Mark Sinkinson Mar 7 '18 at 7:18
  • I'll just leave it here encase it helps others. It helped me – Jeremy Thompson Mar 8 '18 at 10:49

Not the answer you're looking for? Browse other questions tagged or ask your own question.