Compare Two SQL Server Databases using Tsql

Compare Two SQL Server Databases using Tsql

Compare Two SQL Server Databases using Tsql

I’m glad to inform that today I’m going to release my next phase of TsqlTools is SQLCOMPARE. I was in a point to compare two databases which are on two different servers, I have checked many websites and blogs, but unfortunately, I didn’t find any useful T-SQL query for that. But there is so many good software available to compare the databases. Red-Gate is one of my favorite tool for comparison.

So I have prepared 3 Tsql scripts to compare all databases objects between two SQL servers.

1. Tables and Objects

  1. CHECK_CONSTRAINT
  2. DEFAULT_CONSTRAINT
  3. FOREIGN_KEY_CONSTRAINT
  4. PRIMARY_KEY_CONSTRAINT
  5. SECURITY_POLICY
  6. SEQUENCE_OBJECT
  7. SQL_INLINE_TABLE_VALUED_FUNCTION
  8. SQL_SCALAR_FUNCTION
  9. SQL_STORED_PROCEDURE
  10. SQL_TABLE_VALUED_FUNCTION
  11. SQL_TRIGGER
  12. TYPE_TABLE
  13. UNIQUE_CONSTRAINT
  14. USER_TABLE
  15. VIEW
Compare Two SQL Server Databases using Tsql

2. Index Compare

This will compare Indices from both servers and list out missing indices on each server.

 Compare Two SQL Server Databases using Tsql

3. Row Count

This is very straightforward, Just get the row count of all database’s tables from both server and show the difference.

 Compare Two SQL Server Databases using Tsql

How to Run this query?

You can use a centralized Server and create LinkedServers for Source and Target DB servers,
or
Create a LinkedServer for Target server on SourceDB server, then run the query on Source DB server.

Please click the below link to download the scripts.
DOWNLOAD

You may also like this

Leave a Reply

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