لطفا منتظر باشید ...
sp_diff
You may recall from Chapter 4 that we wired VSS's difference-checking tool into Query Analyzer's tools menu so that you could difference check T-SQL scripts. Sp_diff takes a similar approach. It calls VSS's command-line interface, SS.EXE, and accesses its differencing engine to check the differences between two files that you supply. It returns the differences VSS finds between the files as a result set. Here's the code (Listing 21-2):
Listing 21-2 sp_diff.
USE master
GO
IF OBJECT_ID('sp_diff') IS NOT NULL
DROP PROC sp_diff
GO
CREATE PROC sp_diff @file1 sysname='/?', @file2 sysname=NULL
/*
Object: sp_diff
Description: Returns the differences between
two text files as a result set (uses VSS)
Usage: sp_diff @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_diff 'c:\customers.sql', 'c:\customers2.sql'
Created: 2001-01-14. $Modtime: 2001-01-16 $.
*/
AS
SET NOCOUNT ON
IF (COALESCE(@file1+@file2,'/?')='/?') GOTO Help
DECLARE @cmd varchar(1000)
SET @cmd='SS diff '+@file1+' '+@file2+' -Yadmin'
CREATE TABLE #diffs (line int identity, diff varchar(8000))
INSERT #diffs (diff)
EXEC master..xp_cmdshell @cmd
SELECT ISNULL(diff,'') AS diff FROM #diffs
ORDER BY line
DROP TABLE #diffs
RETURN 0
Help:
EXEC sp_usage @objectname='sp_diff',
@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_diff ''c:\customers.sql'',
''c:\customers2.sql'' '
RETURN -1
GO
EXEC sp_diff 'c:\customers.sql',
'c:\customers2.sql'
(Results)
diff
-------------------------Diffing: c:\customers.sql
Against: c:\customers2.sql
6 Change: [CompanyName] [nvarchar] (40)
COLLATE SQL_Latin1_General_CP1_CI_A
To: [Company] [nvarchar] (40) COLLATE
SQL_Latin1_General_CP1_CI_AS NO
15 Del: [Fax] [nvarchar] (24) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
Here, I've supplied two scripts to sp_diff to difference check. It returns the two filenames at the top of the listing, then lists the differences between them. It shows that the CompanyName column was shortened to just Company in the second table, and that line 15 was deleted in the second table. How do we know which table the differences apply to? Look closely at the verbiage at the top of the listing "Diffing…Against." This means that Customer2.SQL is being treated as the master copy of the code, and the listing is displaying the steps you would need to take to make Customer.SQL match it exactly.
As with sp_readtextfile, sp_diff calls on xp_cmdshell to do the real work of the procedure. It uses xp_cmdshell to call SS.EXE, the VSS command-line utility, and passes the two files to it so they can be difference checked. Notice the -Y parameter. This is the user name with which you want to log into VSS. Here I've just supplied the admin user with no password for simplicity's sakeyou'll likely use a different one.
We trap the output from xp_cmdshell in a temporary table, and, as with sp_readtextfile, cleanse the data of NULLs as we return it. The end result is a basic, yet very functional, difference-checking facility from Transact-SQL.
