How would I compare two SQL databases to see what may have changed during an application upgrade?

Kathleen Klosterman
Kathleen Klosterman used Ask the Experts™
on
We are performing an upgrade to an application, and I need to compare the original SQL db to the upgraded db to see what has changed. How would I do this? Not sure of the best way. Is there something like a table compare or maybe at the very least do a record count of the original db, then a record count of the upgraded db. (That wouldn't give me WHAT changed, but just that there might be a different number between the two databases.)  We are a small place and have no DBA.  I am an IT person who knows a little about SQL.  What is the best way to figure out what's changed?

Thank you!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
For example, Toad for MS SQL Server supports this functionality
https://www.toadworld.com/products/toad-for-sql-server

And you could make a script, which extracts data from information_schema and tables to see difference in database object definition and rowcounds, and run such script for both databases. And then compare results in say windiff

Script could be like the following:
SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
       , IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY TABLE_NAME

SELECT CONSTRAINT_NAME, CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
ORDER BY TABLE_NAME, CONSTRAINT_NAME

SELECT name, type_desc, is_unique, is_primary_key
FROM sys.indexes
ORDER BY NAME

Open in new window

Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
You can't with SSMS but there are 3rd party tools that do compare databases. Use your internet search engine to find some of those tools.
Another option is to script both databases to text files and then use a file compare tool to highlight the differences between both files.
My comment contains the full answer with two directions of the problem resolution. The second comment just repeats my comment in other words and does not bring any extra value to the question

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial