IF OBJECT_ID( 'sp_compare' ) IS NOT NULL
DROP PROCEDURE sp_compare
go
CREATE PROCEDURE sp_compare (
@srcdb varchar(92), @destdb varchar(92)
)
AS
/********************************************************************************/
/* Created BY : Umachandar Jayachandran (UC) */
/* Created ON : 21 Jan 1997 */
/* Description: This stored PROCEDURE generates a simple report that */
/* compares 2 databases ON same server or different servers*/
/* The statements for SQL70 databases are provided in */
/* comments. So please enable that for SQL70 installations.*/
/* 1) set concat_null_yields_null off */
/* 2) Column names for identifiers should be made as 128 */
/* characters in length */
/********************************************************************************/
/* Resources : http://www.umachandar.com/resources.htm */
/********************************************************************************/
IF OBJECT_ID( 'tempdb..objects' ) IS NOT NULL
DROP TABLE tempdb..objects
IF OBJECT_ID( 'tempdb..columns' ) IS NOT NULL
DROP TABLE tempdb..columns
IF OBJECT_ID( 'tempdb..changed_tables_cols' ) IS NOT NULL
DROP TABLE tempdb..changed_tables_cols
DECLARE @db1 varchar(61), @db2 varchar(61),
@sp1 varchar(92), @sp2 varchar(92),
@server1 varchar(92), @server2 varchar(92),
@db1command varchar(255), @db2command varchar(255),
@objselect varchar(255), @table varchar(32), @sp varchar(62), @colselectsp varchar(62),
@mesg varchar(255), @count int
SET NOCOUNT ON
-- SQL70 setting to be enabled for proper working of this SP:
-- SET CONCAT_NULL_YIELDS_NULL OFF
SELECT @db1 = CASE WHEN @srcdb LIKE '%.%'
THEN reverse(substring(reverse(@srcdb), 1, charindex('.', reverse(@srcdb)) - 1))
ELSE @srcdb
END ,
@db2 = CASE WHEN @destdb LIKE '%.%'
THEN reverse(substring(reverse(@destdb), 1, charindex('.', reverse(@destdb)) - 1))
ELSE @destdb
END ,
@server1 = CASE WHEN @srcdb LIKE '%.%'
THEN substring(@srcdb, 1, charindex('.', @srcdb))
ELSE NULL
END ,
@server2 = CASE WHEN @destdb LIKE '%.%'
THEN substring(@destdb, 1, charindex('.', @destdb))
ELSE NULL
END,
@objselect = 'select name, object_type = CASE type WHEN ''U'' THEN ''table'' ' +
'when ''P'' THEN ''stored procedure'' when ''V'' then ''view'' END ' +
'from sysobjects WHERE type IN (''U'', ''V'', ''P'')',
@sp = 'master..sp_sqlexec',
@colselectsp = 'exec sp_columns'
SELECT @db1command = stuff(@objselect, charindex('sysobjects', @objselect),
datalength('sysobjects'), @db1 + '..sysobjects'),
@db2command = stuff(@objselect, charindex('sysobjects', @objselect),
datalength('sysobjects'), @db2 + '..sysobjects'),
@sp1 = @server1 + @sp, @sp2 = @server2 + @sp
SELECT @db1command = stuff(@db1command, charindex('name', @db1command),
datalength('name') , '''' + @srcdb + ''', name'),
@db2command = stuff(@db2command, charindex('name', @db2command),
datalength('name') , '''' + @destdb + ''', name')
-- SQL70:
-- CREATE TABLE #objects (db_name varchar(128), name varchar(128), object_type varchar(30))
CREATE TABLE #objects (db_name varchar(61), name varchar(30), object_type varchar(30))
INSERT #objects EXEC @sp1 @db1command
INSERT #objects EXEC @sp2 @db2command
DELETE #objects WHERE name IN ('objects', 'columns', 'changed_tables_cols', 'upgrade_status')
SELECT @mesg = '1. Tables present ONLY IN database: ' + @srcdb
PRINT @mesg
SELECT name FROM #objects
WHERE object_type = 'table' AND db_name = @srcdb and
name NOT IN (select name FROM #objects WHERE object_type = 'table' AND db_name = @destdb)
ORDER BY name
PRINT ''
SELECT @mesg = '2. Tables present ONLY IN database: ' + @destdb
PRINT @mesg
SELECT name FROM #objects
WHERE object_type = 'table' AND db_name = @destdb and
name NOT IN (select name FROM #objects WHERE object_type = 'table' AND db_name = @srcdb)
ORDER BY name
PRINT ''
SELECT @mesg = 'Upgrade status table:'
PRINT @mesg
PRINT 'create TABLE upgrade_status'
PRINT '('
PRINT 'name varchar(30) NOT null,'
PRINT 'status varchar(10) NOT NULL
CHECK (status IN (''INCOMPLETE'', ''COMPLETE'')) DEFAULT ''INCOMPLETE'''
PRINT ')'
PRINT 'go'
PRINT @mesg
SELECT @mesg = 'insert upgrade_status select name, ''INCOMPLETE'' FROM sysobjects ' +
'where type = ''U'' AND name NOT IN (''upgrade_status'')'
PRINT @mesg
PRINT ''
SELECT @mesg = 'Drop statements FOR the tables IN the database: ' + @destdb
PRINT @mesg
DECLARE drop_tables CURSOR FOR
SELECT name FROM #objects
WHERE object_type = 'table' AND db_name = @destdb and
name NOT IN (select name FROM #objects WHERE object_type = 'table' AND db_name = @srcdb)
ORDER BY name
OPEN drop_tables
WHILE( 'FETCH IS OK' = 'FETCH IS OK' )
BEGIN
FETCH NEXT FROM drop_tables INTO @table
IF @@FETCH_STATUS < 0 BREAK
SELECT @mesg = 'print ''Dropping TABLE ' + @table + '..'''
PRINT @mesg
PRINT 'begin tran'
SELECT @mesg = 'if EXISTS (select name FROM upgrade_status WHERE name = ''' + @table +
''' AND status = ''INCOMPLETE'')'
PRINT @mesg
PRINT 'begin'
SELECT @mesg = ' DROP TABLE ' + @table
PRINT @mesg
PRINT ' IF @@error <> 0'
PRINT ' begin'
PRINT ' IF @@trancount > 0'
PRINT 'rollback tran'
PRINT ' end'
PRINT ' else'
PRINT ' begin'
SELECT @mesg = ' UPDATE upgrade_status SET status = ''COMPLETE'' WHERE name = ''' +
@table + ''''
PRINT @mesg
PRINT ' COMMIT tran'
PRINT ' end'
PRINT 'end'
PRINT ''
END
CLOSE drop_tables
DEALLOCATE drop_tables
PRINT ''
-- goto END_LABEL
SELECT @mesg = '3. Analyzing tables...'
PRINT @mesg
PRINT ''
-- SQL70:
/*
CREATE TABLE #columns (
TABLE_QUALIFIER varchar(128) NULL, TABLE_OWNER varchar(128),
TABLE_NAME varchar(128), COLUMN_NAME varchar(128),
DATA_TYPE smallint NULL, TYPE_NAME varchar(128), PREC int,
LENGTH int, SCALE smallint NULL, RADIX smallint NULL,
NULLABLE smallint, REMARKS varchar(254) NULL,
COLUMN_DEF varchar(8000) NULL, SQL_DATA_TYPE smallint,
SQL_DATETIME_SUB smallint NULL, CHAR_OCTET_LENGTH int NULL,
ORDINAL_POSITION int, IS_NULLABLE varchar(254), SS_DATA_TYPE tinyint
)
*/
CREATE TABLE #columns (
TABLE_QUALIFIER varchar(32) NULL, TABLE_OWNER varchar(32),
TABLE_NAME varchar(32), COLUMN_NAME varchar(32),
DATA_TYPE smallint NULL, TYPE_NAME varchar(13), PREC int,
LENGTH int, SCALE smallint NULL, RADIX smallint NULL,
NULLABLE smallint, REMARKS varchar(254) NULL,
COLUMN_DEF varchar(254) NULL, SQL_DATA_TYPE smallint,
SQL_DATETIME_SUB smallint NULL, CHAR_OCTET_LENGTH int NULL,
ORDINAL_POSITION int, IS_NULLABLE varchar(254), SS_DATA_TYPE tinyint
)
DECLARE common_tables scroll CURSOR FOR
SELECT name FROM #objects WHERE object_type = 'table'
GROUP BY name HAVING count(name) = 2
OPEN common_tables
WHILE( 'FETCH IS OK' = 'FETCH IS OK' )
BEGIN
FETCH NEXT FROM common_tables INTO @table
IF @@FETCH_STATUS < 0 BREAK
SELECT @db1command = 'use' + space(1) + @db1 + space(1) + @colselectsp + space(1) + @table,
@db2command = 'use' + space(1) + @db2 + space(1) + @colselectsp + space(1) + @table
INSERT #columns EXEC @sp1 @db1command
INSERT #columns EXEC @sp2 @db2command
END
CLOSE common_tables
DEALLOCATE common_tables
SELECT space(128) AS TABLE_QUALIFIER, TABLE_NAME, COLUMN_NAME, space(128) AS TYPE_NAME
INTO #changed_tables_cols
FROM #columns
GROUP BY TABLE_NAME, COLUMN_NAME
HAVING COUNT(*) = 1
UPDATE c1
SET c1.TABLE_QUALIFIER = c2.TABLE_QUALIFIER
FROM #changed_tables_cols c1, #columns c2
WHERE c1.TABLE_NAME = c2.TABLE_NAME AND c1.COLUMN_NAME = c2.COLUMN_NAME
SELECT @count = 1
DECLARE changed_tables CURSOR FOR
SELECT DISTINCT TABLE_NAME FROM #changed_tables_cols
OPEN changed_tables
WHILE('FETCH IS OK' = 'FETCH IS OK')
BEGIN
FETCH NEXT FROM changed_tables INTO @table
IF @@fetch_status < 0 BREAK
SELECT @count = @count + 1,
@mesg = ltrim(str(@count)) + ') Table: ' + @table
PRINT @mesg
SELECT @mesg = 'Database: ' + @db1
IF EXISTS(SELECT COLUMN_NAME FROM #changed_tables_cols
WHERE TABLE_NAME = @table AND TABLE_QUALIFIER = @db1)
BEGIN
PRINT @mesg
SELECT c.COLUMN_NAME, c.TYPE_NAME, c.LENGTH, c.IS_NULLABLE, c.COLUMN_DEF
FROM #columns c, #changed_tables_cols c1
WHERE c1.TABLE_NAME = @table AND c1.TABLE_QUALIFIER = @db1
AND c1.TABLE_NAME = c.TABLE_NAME
AND c1.TABLE_QUALIFIER = c.TABLE_QUALIFIER
AND c1.COLUMN_NAME = c.COLUMN_NAME
PRINT ''
END
SELECT @mesg = 'Database: ' + @db2
IF EXISTS(SELECT COLUMN_NAME FROM #changed_tables_cols
WHERE TABLE_NAME = @table AND TABLE_QUALIFIER = @db2)
BEGIN
PRINT @mesg
SELECT c.COLUMN_NAME, c.TYPE_NAME, c.LENGTH, c.IS_NULLABLE, c.COLUMN_DEF
FROM #columns c, #changed_tables_cols c1
WHERE c1.TABLE_NAME = @table AND c1.TABLE_QUALIFIER = @db2
AND c1.TABLE_NAME = c.TABLE_NAME
AND c1.TABLE_QUALIFIER = c.TABLE_QUALIFIER
AND c1.COLUMN_NAME = c.COLUMN_NAME
PRINT ''
END
FETCH NEXT FROM changed_tables INTO @table
END
CLOSE changed_tables
DEALLOCATE changed_tables
/*
-- get the other datatype changes.
INSERT #changed_tables_cols
SELECT space(32) AS TABLE_QUALIFIER, TABLE_NAME, COLUMN_NAME ,
TYPE_NAME
FROM #columns GROUP BY TABLE_NAME, COLUMN_NAME, TYPE_NAME HAVING
COUNT(*) = 1
UPDATE c1
SET c1.TABLE_QUALIFIER = c2.TABLE_QUALIFIER
FROM #changed_tables_cols c1, #columns c2
WHERE c1.TABLE_NAME = c2.TABLE_NAME AND c1.COLUMN_NAME = c2.COLUMN_NAME
and
c1.TYPE_NAME = c2.TYPE_NAME
DELETE #changed_tables_cols WHERE TYPE_NAME IS NOT NULL AND COLUMN_NAME
IN ('mod_date', 'mod_user')
*/
SELECT @mesg = '4. Stored procedures present ONLY IN database: ' + @srcdb
PRINT @mesg
SELECT name FROM #objects
WHERE object_type = 'stored procedure' AND db_name = @srcdb and
name NOT IN (select name FROM #objects
WHERE object_type = 'stored procedure' AND db_name = @destdb)
PRINT ''
SELECT @mesg = '5. Stored procedures present ONLY IN database: ' + @destdb
PRINT @mesg
SELECT name FROM #objects
WHERE object_type = 'stored procedure' AND db_name = @destdb and
name NOT IN (select name FROM #objects
WHERE object_type = 'stored procedure' AND db_name = @destdb)
PRINT ''
SELECT * INTO tempdb..objects FROM #objects
SELECT * INTO tempdb..columns FROM #columns
SELECT * INTO tempdb..changed_tables_cols FROM #changed_tables_cols
END_LABEL:
PRINT 'Comparison of the databases completed.'
go