Comparing SQL Databases for Differences in Objects

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)
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!

Tags: , , , , , , , , , | Posted under Programming/Coding | RSS 2.0

Author Spotlight

David Bauernschmidt

David Bauernschmidt

I live in the historical triangle of Virginia where I am married with two daughters. I have spent over 13 years working for a Fortune 500 company in the computer area. I started in VB 6.0 and by the time I ended my employment I was supervising a development team where we built many web applications. When my first daughter was born I wanted to spend more time with her so I left and became a programmer analyst for local government as well as launch my own company. Since then I have grown James River Webs into a profitable web design and application company helping small businesses create a big presence on the internet. As an employee I have created web application used by citizens and other companies. I enjoy fly fishing, and spending time with my family. I also enjoy learning new approaches and development tools when it comes to developing applications.

Leave a Reply

Your email address will not be published. Required fields are marked *


× 1 = 4

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>