لطفا منتظر باشید ...
sp_diffdb
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.
