Dynamically compary 2 tables using EXCEPT and INFORMATION_SCHEMA

Question

Dynamically compary 2 tables using EXCEPT and INFORMATION_SCHEMA

ss1011

Old Hand

Points: 344
More actions
September 5, 2011 at 1:59 am

#256618

Hi,
I'm trying to find a way to pass 2 database names into a procedure that will automatically compare all tables. I'm guessing that using the TABLE_NAME from INFORMATION_SCHEMA and some type of cursor that loops through the 2nd table.
I need to compare all tables across 2 db's (pre-migrate to post-migrate) as a form of verifying the migration.
Can someone provide some guidance?
:crazy:
Thank you.
Sid
-Sidster
"You can have it done cheap, done well, or done fast. Pick two."

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

chris.mcgowan SSCarpal Tunnel Points: 4269 More actions · Answer 1

Is it simply table names which you are comparing accross the two instances? Or are you looking to compare table definitions as well?

Thanks

Chris

------------------------
I am the master of my fate:
I am the captain of my soul.
------------------------
Blog: http://sqlserver365.blogspot.co.uk/
Twitter: @ckwmcgowan
------------------------

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

Consider utilizing the built in tool available in SQL 2005 and 2008.

Use BOL and search for TABLEDIFF - other than that REDGATE has a tool available for a free 14 day trial.

http://www.red-gate.com/products/sql-development/sql-compare/?utm_source=google&utm_medium=cpc&utm_content=unmet_need&utm_campaign=sqlcompare&gclid=CPnHnueghqsCFeoEQAodWR7w2w

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

chris.mcgowan SSCarpal Tunnel Points: 4269 More actions · Answer 3

TABLEDIFF is for use on a server in a replication topology. It is used to determine if published data for table articles at the Publisher and Subscriber are not identical.

You could use a powershell script to pipe the table names to a text file on each server and verify that they are the same. Going one further you could script the definition of the tables on each server and verify they are the same.

The easiest way would be to use a DB comparison tool though.

Thanks

Chris

------------------------
I am the master of my fate:
I am the captain of my soul.
------------------------
Blog: http://sqlserver365.blogspot.co.uk/
Twitter: @ckwmcgowan
------------------------

ss1011 Old Hand Points: 344 More actions · Answer 4

Hi Chris,

Just tables, no definitions.

Our concerns are with data and not so much the definition.

Although I wouldn't mind having both in my back pocket just in case...down the road. :hehe:

-Sidster
"You can have it done cheap, done well, or done fast. Pick two."

ss1011 Old Hand Points: 344 More actions · Answer 5

Will do. Good info.

-Sidster
"You can have it done cheap, done well, or done fast. Pick two."