Currently I use RedGate’s SQL Data Compare to see differences in data across databases. It’s a great product. One thing I love about RedGate is the usability of their products. All of them are intuitive and easy to use. What if you don’t have the cash to do this though?

You could use the EXCEPT (introduced in SQL Server 2005) clause to do your comparison. For example:

  SELECT  *  
  FROM    #table2 -- returns 30 records
 
  EXCEPT
 
  SELECT  *  
  FROM    #table1 -- returns 10 records.

You get a result set of 20 records if those 10 records in the table1 statement are in table2. You can think of it as subtracting records from table2 that are in table1. You will get the end result of 20 records.

The following statement builds on that taking the end result and putting it back into table1. This is useful if you want to sync an outdated table (in this case table1) with another table that has additional records (table2).

INSERT INTO #table1
  SELECT  *  
  FROM    #table2 -- latest data table (we'll be copying from here)
 
  EXCEPT
 
  SELECT  *
  FROM    #table1 -- outdated table (has old data)

Other tools to compare data across tables: CompareData may let you do what you want, and its table compare is free unlimited, I believe, for the evaluation version. The great thing about this tool is also that it will check results of two SQL statements / stored procs. Useful when unit testing.

Also you can use TableDiff.exe in your “C:\Program Files\Microsoft SQL Server\90\COM” directory. Learn more about it at the SQL team website.

One handy trick that I used to do was use the function OBJECT_DEFINITION() to view the code of a sproc. It’s a handy little function that doesn’t get much love as OBJECT_ID or OBJECT_NAME(). Let’s say we want to get the code of the sproc dbo.uspGetBillOfMaterials from DB AdventureWorks. We do like so:

SELECT OBJECT_DEFINITION( OBJECT_ID('dbo.uspGetBillOfMaterials') )

You get a result that may not be too pretty. In this case, use PRINT rather than SELECT, since SSMS strips out line breaks to squeeze it in the cell.

Now let’s search all available user sprocs in the database to search for the string “%GetBillOf% :

SELECT  OBJECT_DEFINITION( p.[OBJECT_ID] ) AS code 
FROM    sys.procedures p
WHERE   OBJECT_DEFINITION( p.[OBJECT_ID] ) LIKE '%GetBillOf%'

Now, I do longer search this way since every day I have a job that writes out all DB objects (tables, sprocs, etc.) to a individual files and they get indexed via Copernic Desktop Search. I script them out using SQL Compare.

Unfortunately, I don’t know why, OBJECT_DEFINITION() doesn’t work on tables. It’s been a wanted feature by the community. If you want to learn more about this function, check out:

• How to script all stored procedures in a database
• Script Table definitions using TSQL

If you haven’t heard of SQLCompare by RedGate, you’re missing out. It’s an amazing product. In summary, you can do the following (which I use it for 99% of the time):

• Compare Schema / TSQL code from Different Databases
• Syc Schema / TSQL code across two databases
• Generate TSQL code from your comparison so you can use it for deployments
• Generate reports on SQL Changes

Most of those things can be done via the GUI version of the tool. The product also comes with a command line version, which you can use it in your build script.

To script out your entire database via the command console, you can do the following:

SQLCompare /force /database1:YOURDATABASENAME /username1:sa /password1:password /server1:YOURDATABASESERVERNAME /makescripts:c:\x

To compare two databases and generate a report via the command console, you can do the following. The reason there’s so many switches is because you need to enter the database name and credentials for the two databases.

SQLCompare /force /database1:DB1NAME /username1:sa /password1:password /server1:SERVER1NAME /database2:DB2NAME /username2:sa /password2:password /server2:SERVER2NAME /report:c:\report.html /reporttype:Interactive