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

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!
Kathleen KlostermanSystems AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

OMC2000Commented:
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

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
OMC2000Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.