Compare Database Objects from Multiple Instances

Question

Compare Database Objects from Multiple Instances

pawana.paul

SSCommitted

Points: 1686
More actions
April 27, 2012 at 11:28 am

#273307

Dear Friends,
I am planning to compare database objects from Multiple Instances. Details are given below:
1. Instance ABC have around 10 Databases and 1000 objects
2. Instance XYZ have around 10 Databases and 700 objects
How to find missing 300 objects in XYZ instance?
Your quick help will be highly appreciated.
Regards,
Paul

Viewing 12 posts - 1 through 11 (of 11 total)

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

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 1

Honest answer here, if I had to do that, I'd use the command-line utility built into SQL Compare from Red Gate. It's the easiest way I know to make this happen in a timely & efficient manner.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

pawana.paul SSCommitted Points: 1686 More actions · Answer 2

Thank for your response.

I am looking at SQL Script with the combination of Linked Server.

I am not familiar with Redgate and there will be licencing issue to use in my company.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

I second the use of SQLCompare. It will likely work out cheaper to buy a license than to develop in-house.

If you absolutely must do custom dev, maybe a C#/VB app and use the SMO libraries. T-SQL comparisons will be difficult and tedious (been there, done that). If you're solely wanting to see what's in one place and not the other, that's pretty easy. If you need to compare teh definitions, see what's different between objects on the two instances that's very complex to do properly.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

pawana.paul SSCommitted Points: 1686 More actions · Answer 4

Thank You Gila,

I am trying to workaround in TSQL using linked server as getting SQLCompare at this point will be difficult and I can't wait for long. The logic I am trying is given below:

Bring all the Database Objects of ABC instance to 1 single table

Bring all the Database Objects of XYZ instance to 1 single table

use simple join with not equal to condiation

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Are you solely trying to see what objects are on one instance and not the other? No checks for whether objects named the same have the same definition, no checks for objects with the same definition but different names?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

pawana.paul SSCommitted Points: 1686 More actions · Answer 6

Yes. Only trying to see what objects are on one instance and not the other. That is what is my requirement. Please let me know if you have better option than what I mentioned above without using any other tool.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

What you suggest will work.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 8

pawana.paul (4/30/2012)
Yes. Only trying to see what objects are on one instance and not the other. That is what is my requirement. Please let me know if you have better option than what I mentioned above without using any other tool.

just basic objects?

something like this seems to work for me:

--returns items existng on Sandbox that are not in Test

SELECT SCHEMA_NAME(SCHEMA_ID),name,type_desc

FROM DEV223.Sandbox.sys.objects

WHERE is_ms_shipped=0

AND type_desc IN ( 'SQL_SCALAR_FUNCTION', 'CLR_SCALAR_FUNCTION', 'CLR_TABLE_VALUED_FUNCTION', 'SQL_INLINE_TABLE_VALUED_FUNCTION',

'SQL_STORED_PROCEDURE', 'CLR_STORED_PROCEDURE', 'RULE', 'SQL_TABLE_VALUED_FUNCTION',

'USER_TABLE', 'VIEW' )

EXCEPT

SELECT SCHEMA_NAME(SCHEMA_ID),name,type_desc

FROM DEV223.Test.sys.objects

WHERE is_ms_shipped=0

AND type_desc IN ( 'SQL_SCALAR_FUNCTION', 'CLR_SCALAR_FUNCTION', 'CLR_TABLE_VALUED_FUNCTION', 'SQL_INLINE_TABLE_VALUED_FUNCTION',

'SQL_STORED_PROCEDURE', 'CLR_STORED_PROCEDURE', 'RULE', 'SQL_TABLE_VALUED_FUNCTION',

'USER_TABLE', 'VIEW' )

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

pawana.paul SSCommitted Points: 1686 More actions · Answer 9

Thanks for the script. This script is for comparision of database objects with 2 databases on same instance and database names are coded. Is it possible to use MSforeachDB stored procedure for both the instances so that I can get the comparision in single query execution. If yes, please provide me the script.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Create a utilities database on each server. Use MSForEachDB on both instances to create insert a list of all objects in all databases into a table in tht utilities database, then use Lowell's code to compare them

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

pawana.paul SSCommitted Points: 1686 More actions · Answer 11

Thanks Gail and Lowell for your help. Let me try using both of your logic / code.

Pawana Paul