Table name comparison - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Table name comparison

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2010-08-05 : 04:37:53

Hi,

1)I have two database (sample1,sample 2).I have to compare the sample1 db tables(user tables not sys tables) with sample2 db tables and wanted to list the table not exist which is exist in sample1 db.

2)I have two database (sample1,sample 2).I have to compare the sample1 db stored procedures(user tables not sys tables) with sample2 db stored procedures and wanted to list the stored procedures not exist which is exist in sample1 db.

please show me some smaple query for this.

thanks in advance

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-05 : 04:58:09

SELECT a.name [Sample2 Table Names] FROM Sample2.sys.tables a
LEFT JOIN Sample1.sys.tables b ON a.name = b.name
WHERE b.name is null

SELECT a.name [Sample2 Procedure Names] FROM Sample2.sys.procedures a
LEFT JOIN Sample1.sys.procedures b ON a.name = b.name
WHERE b.name is null

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Kristen
Test

22859 Posts

Posted - 2010-08-05 : 05:15:01

Or possibly (answering a different question to the O/P's

)


SELECT a.name [Sample2 Table Names] , b.name AS [Sample1 Table Names]
FROM Sample2.sys.tables a
FULL OUTER JOIN Sample1.sys.tables b ON a.name = b.name
WHERE b.name is null OR a.name is null

ditto for second query.

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2010-08-05 : 05:25:33

thanks a lot Vaibhav

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2010-08-05 : 05:27:00

thanks kristen

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-05 : 05:29:53

Very nice Kristen...

Single query for all types of objects -


SELECT a.Name, a.type, b.name, b.type FROM sample2.sys.objects a
FULL OUTER JOIN sample1.sys.objects b ON a.name = b.name and a.type = b.type
WHERE a.Type IN ('P','U') or b.Type IN ('P','U')

Change the type to get the desired output...

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources