sp_diffdb

for RuBoard

This last example is kind of the grand finale (well, as close as we'll get) for this chapter. It makes use of a couple of the routines we looked at earlier in the chapter to provide a capability that is surprisingly absent from the SQL Server product itselfnamely, the ability to check the differences between two databases. I probably get at least one e-mail message a week on this subject alone. People are always wanting to know how to determine the differences between two versions of a given database. Although there are third-party tools that provide this functionality, it tends to be a bit on the expensive side. I need this ability frequently myself , so I built a stored procedure that can check the schema of one database against that of another. It's completely automated and surprisingly functional.

How does it work? Recall the sp_diff and sp_generate_script procedures from earlier in the chapter. Sp_diffdb simply uses sp_generate_script to generate scripts for the databases you want to compare, then calls sp_diff to check them for differences. Clever, eh? Here's the code (Listing 21-10):

Listing 21-10 sp_diffdb.
 USE master GO IF OBJECT_ID('sp_diffdb') IS NOT NULL   DROP PROC sp_diffdb GO CREATE PROC sp_diffdb @DB1 sysname='/?', @DB2 sysname=NULL,        @TempPath sysname='C:\TEMP',        @server sysname='(local)',       -- Name of the server to connect to        @username sysname='sa',          -- Name of the user to connect as        (defaults to 'sa')        @password sysname=NULL,          -- User's password   @trustedconnection bit=1       -- Use a trusted connection to connect to the server /* Object: sp_diffdb Description: Returns the differences between two text files as a result set (uses VSS) Usage: sp_diffdb @file1=full path to first file, @file2=fullpath to second file Returns: (None) $Author: Ken Henderson $. Email: khen@khen.com $Revision: 1.0 $ Example: sp_diffdb 'c:\customers.sql', 'c:\customers2.sql' Created: 2001-01-14. $Modtime: 2001-01-16 $. */ AS SET NOCOUNT ON IF (COALESCE(@DB1+@DB2,'/?')='/?') GOTO Help DECLARE @cmd varchar(1000), @cmdout varchar(1000), @trustcon char(1), @file1 sysname, @file2 sysname SET @trustcon=CAST(@trustedconnection AS char(1)) IF RIGHT(@TempPath,1)<>'\' SET @TempPath=@TempPath+'\' SET @file1=@TempPath+@DB1+'.SQL' SET @cmd=@DB1+'..sp_generate_script @includeheaders=0, @resultset=0, @outputname='''+@file1+''', @server='''+@server+''', @username='''+@username+''''+ISNULL(', @password='''+@password+'''','')+', @trustedconnection='+@trustcon EXEC(@cmd) print @cmd SET @file2=@TempPath+@DB2+'.SQL' SET @cmd=@DB2+'..sp_generate_script @includeheaders=0, @resultset=0, @outputname='''+@file2+''', @server='''+@server+''', @username='''+@username+''''+ISNULL(', @password='''+@password+'''','')+', @trustedconnection='+@trustcon EXEC(@cmd) EXEC sp_diff @file1, @file2 RETURN 0 Help: EXEC sp_usage @objectname='sp_diffdb', @desc='Returns the differences between two text files as a result set (uses VSS)', @parameters='@file1=full path to first file, @file2=fullpath to second file', @author='Ken Henderson', @email='khen@khen.com', @version='1',@revision='0', @datecreated='20010114', @datelastchanged='20010116', @example='sp_diffdb ''c:\customers.sql'', ''c:\customers2.sql'' ' RETURN -1 GO 

Because sp_diffdb calls sp_generate_script, which in turn connects to the server separately, we have to supply login info to the procedure. Other than that, the two key parameters to the routine are the names of the two databases you want to compare. Here's an example of a call to sp_diffdb (Listing 21-11):

Listing 21-11 sp_diffdb can report the differences between two databases.
 EXEC sp_diffdb 'northwind','northwind5' diff --------------------------------------------------------------------------- Diffing: C:\TEMP\northwind.SQL Against: C:\TEMP\northwind5.SQL  94    Del: CREATE TABLE [dbo].[cust] (  95    Del:  [CustNo] [int] IDENTITY (1, 1) NOT NULL ,  96    Del:  [City] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,  97    Del:  [State] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL  98    Del:  ) ON [PRIMARY]  99    Del:  GO 100    Del: 101    Del:  CREATE CLUSTERED INDEX [citystate] ON [dbo].[cust]([City], [Sta 102    Del: GO 103    Del: 104    Del: 

Here, we can see that the schema for Northwind5 is missing the cust table. As I said earlier in the discussion on sp_diff, the second of the two files passed to sp_diff is considered the master copy: The output from sp_diff lists the steps necessary to make the first file match the second one. From this output, we can infer that the Northwind database has a table named cust, whereas Northwind5 does not. Let's see what happens when we compare identical databases (Listing 21-12):

Listing 21-12 sp_diffdb can detect when there are no differences between two databases.
 EXEC sp_diffdb 'northwind','northwind4' diff --------------------------------------------------------------------------- Diffing: C:\TEMP\northwind.SQL Against: C:\TEMP\northwind4.SQL No differences. 

Although not a graphical tool, sp_diffdb's ability to check for differences between two different databases is a powerful capability and should come in handy in a good number of situations.

for RuBoard


The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML
The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML
ISBN: 201700468
EAN: N/A
Year: 2005
Pages: 223

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net