Like the real world, development practices keep changing so when I find development code that can make my life easier I want to save it for future use. In this article I want to show you how you can compare any object in SQL to another database object. I use this to simply compare the date modified between our Production and Test database. You can use this for a multitude of different purposes. But you get the idea. Once you get this you could actually then add filters to only show differences.

Note: Normally using Unions with large tables will decrease performance but for most medium size databases this would work just fine.

[code language=”sql”]
Select Name, type, max(table1create_date) as Table1_create_date, max(table1_modify_date) as Table1_modify_date
,max(table2create_date) as Table2_Create_Date, max(table2modify_date) as Table2_modify_date from (
Select
Name,
type,
create_date as table1create_date,
modify_date as table1_modify_date,
null as table2create_date,
null as table2modify_date
From
[tablename1].sys.objects

Union All

Select
Name,
type,
null as table1create_date,
null as table1_modify_date,
create_date as table2_modify_date,
modify_date as table2create_date
From
[tablename2].sys.objects
) ee group by Name,type
order by Name
[/code]

The Valid Types of Database Objects are as follows;

Looking for quality web hosting? Look no further than Arvixe Web Hosting!