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;
AF | Aggregate function (CLR) |
C | CHECK constraint |
D | Default or Default constraint |
F | Foreign Key Constraint |
FN | Scalar Function |
FS | Assembly (CLR) scalar-function |
FT | Assemble (CLR) table valued function |
IT | Internal Table |
K | Primary Key or Unique Constraint |
L | Log |
P | Stored Procedure |
PC | Assembly (CLR) |
R | Rule |
RF | Replication |
S | System Table |
SN | Synonym |
SQ | Service Que |
TA | Assembly (CLR) DML Trigger |
TF | Table Function |
TR | SQL DML Trigger |
TT | Table Type |
U | User Table |
V | View |
X | Extended Stored Procedure |
Looking for quality web hosting? Look no further than Arvixe Web Hosting!