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.
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
The Valid Types of Database Objects are as follows;
|AF||Aggregate function (CLR)|
|D||Default or Default constraint|
|F||Foreign Key Constraint|
|FS||Assembly (CLR) scalar-function|
|FT||Assemble (CLR) table valued function|
|K||Primary Key or Unique Constraint|
|TA||Assembly (CLR) DML Trigger|
|TR||SQL DML Trigger|
|X||Extended Stored Procedure|
Looking for quality web hosting? Look no further than Arvixe Web Hosting!