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 :     https://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
This page was last updated on May 01, 2006 04:28 PM.