This Stored Procedure compares the structures of two databases. Compares
if all tables in one database have an analog (by name) in the second
database and also compares the structures of the tables.
USE Master
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_CompareDB' AND TYPE = 'P')
DROP PROC sp_CompareDB
GO
--------------------------------------------------------------------------------------------
-- sp_CompareDB 'Database1', 'Database2'
--
-- The SP compares structures in 2 databases.
-- 1. Compares if all tables in one database have analog (by name) in second database
-- Tables not existing in one of databases won't be used for data comparing
-- 2. Compares if structures for tables with the same names are the same. Shows structural
-- differences like:
-- authors
-- Column Phone: in db1 - char(12), in db2 - char(14)
-- sales
-- Column Location not in db2
-- Tables, having different structures, won't be used for data comparing. However if the tables
-- contain columns of the same type and different length (like Phone in the example above) or
-- tables have compatible data types (have the same type in syscolumns - char and nchar,
-- varchar and nvarchar etc) they will be allowed for data comparing.
--------------------------------------------------------------------------------------------
-- Parameters:
-- 1. @db1 - name of first database to compare
-- 2. @db2 - name of second database to compare
-- 3. @TabList - list of tables to compare. if empty - all tables in the databases should be
-- compared
-- 4. @NumbToShow - number of rows with differences to show. Default - 10.
-- 5. @OnlyStructure - flag, if set to 1, allows to avoid data comparing. Only structures should
-- be compared. Default - 0
-- 6. @NoTimestamp - flag, if set to 1, allows to avoid comparing of columns of timestamp
-- data type. Default - 0
-- 7. @VerboseLevel - if set to 1 allows to print queries used for data comparison
--------------------------------------------------------------------------------------------
CREATE PROC sp_CompareDB
(
@db1 VARCHAR(128),
@db2 VARCHAR(128),
@OnlyStructure BIT = 0,
@TabList VARCHAR(8000) = '',
@NumbToShow INT = 10,
@NoTimestamp BIT = 0,
@VerboseLevel TINYINT = 0
)
AS
BEGIN
SET NOCOUNT ON
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON
IF @OnlyStructure <> 0 SET @OnlyStructure = 1
IF @NoTimestamp <> 0 SET @NoTimestamp = 1
IF @VerboseLevel <> 0 SET @VerboseLevel = 1
DECLARE @sqlStr VARCHAR(8000)
-- Checking if there are specified databases
DECLARE @SrvName sysname
DECLARE @DBName sysname
SET @db1 = RTRIM(LTRIM(@db1))
SET @db2 = RTRIM(LTRIM(@db2))
SET @SrvName = @@SERVERNAME
IF CHARINDEX('.',@db1) > 0
BEGIN
SET @SrvName = LEFT(@db1,CHARINDEX('.',@db1)-1)
IF NOT EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname = @SrvName)
BEGIN
PRINT 'There is no linked server named '+@SrvName+'. End of work.'
RETURN
END
SET @DBName = RIGHT(@db1,LEN(@db1)-CHARINDEX('.',@db1))
END
ELSE
SET @DBName = @db1
EXEC ('declare @Name sysname select @Name=name from ['+@SrvName+'].master.dbo.sysdatabases where name = '''+@DBName+'''')
IF @@ROWCOUNT = 0
BEGIN
PRINT 'There is no database named '+@db1+'. End of work.'
RETURN
END
SET @SrvName = @@SERVERNAME
IF CHARINDEX('.',@db2) > 0
BEGIN
SET @SrvName = LEFT(@db2,CHARINDEX('.',@db2)-1)
IF NOT EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname = @SrvName)
BEGIN
PRINT 'There is no linked server named '+@SrvName+'. End of work.'
RETURN
END
SET @DBName = RIGHT(@db2,LEN(@db2)-CHARINDEX('.',@db2))
END
ELSE
SET @DBName = @db2
EXEC ('declare @Name sysname select @Name=name from ['+@SrvName+'].master.dbo.sysdatabases where name = '''+@DBName+'''')
IF @@ROWCOUNT = 0
BEGIN
PRINT 'There is no database named '+@db2+'. End of work.'
RETURN
END
PRINT REPLICATE('-',LEN(@db1)+LEN(@db2)+25)
PRINT 'Comparing databases '+@db1+' and '+@db2
PRINT REPLICATE('-',LEN(@db1)+LEN(@db2)+25)
PRINT 'Options specified:'
PRINT ' Compare only structures: '+CASE WHEN @OnlyStructure = 0 THEN 'No' ELSE 'Yes' END
PRINT ' List of tables to compare: '+CASE WHEN LEN(@TabList) = 0 THEN ' All tables' ELSE @TabList END
PRINT ' Max number of different rows in each table to show: '+LTRIM(STR(@NumbToShow))
PRINT ' Compare timestamp columns: '+CASE WHEN @NoTimestamp = 0 THEN 'No' ELSE 'Yes' END
PRINT ' Verbose level: '+CASE WHEN @VerboseLevel = 0 THEN 'Low' ELSE 'High' END
-----------------------------------------------------------------------------------------
-- Comparing structures
-----------------------------------------------------------------------------------------
PRINT CHAR(10)+REPLICATE('-',36)
PRINT 'Comparing structure of the databases'
PRINT REPLICATE('-',36)
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name LIKE '#TabToCheck%')
DROP TABLE #TabToCheck
CREATE TABLE #TabToCheck (name sysname)
DECLARE @NextCommaPos INT
IF LEN(@TabList) > 0
BEGIN
WHILE 1=1
BEGIN
SET @NextCommaPos = CHARINDEX(',',@TabList)
IF @NextCommaPos = 0
BEGIN
SET @sqlstr = 'insert into #TabToCheck values('''+@TabList+''')'
EXEC (@sqlstr)
BREAK
END
SET @sqlstr = 'insert into #TabToCheck values('''+LEFT(@TabList,@NextCommaPos-1)+''')'
EXEC (@sqlstr)
SET @TabList = RIGHT(@TabList,LEN(@TabList)-@NextCommaPos)
END
END
ELSE -- then will check all tables
BEGIN
EXEC ('insert into #TabToCheck select name from '+@db1+'.dbo.sysobjects where type = ''U''')
EXEC ('insert into #TabToCheck select name from '+@db2+'.dbo.sysobjects where type = ''U''')
END
-- First check if at least one table specified in @TabList exists in db1
EXEC ('declare @Name sysname select @Name=name from '+@db1+'.dbo.sysobjects where name in (select * from #TabToCheck)')
IF @@ROWCOUNT = 0
BEGIN
PRINT 'No tables in '+@db1+' to check. End of work.'
RETURN
END
-- Check if tables existing in db1 are in db2 (all tables or specified in @TabList)
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name LIKE '#TabNotInDB2%')
DROP TABLE #TabNotInDB2
CREATE TABLE #TabNotInDB2 (name sysname)
INSERT INTO #TabNotInDB2
EXEC ('select name from '+@db1+'.dbo.sysobjects d1o '+
'where name in (select * from #TabToCheck) and '+
' d1o.type = ''U'' and not exists '+
'(select * from '+@db2+'.dbo.sysobjects d2o'+
' where d2o.type = ''U'' and d2o.name = d1o.name) order by name')
IF @@ROWCOUNT > 0
BEGIN
PRINT CHAR(10)+'The table(s) exist in '+@db1+', but do not exist in '+@db2+':'
SELECT * FROM #TabNotInDB2
END
DELETE FROM #TabToCheck WHERE name IN (SELECT * FROM #TabNotInDB2)
DROP TABLE #TabNotInDB2
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name LIKE '#TabNotInDB1%')
DROP TABLE #TabNotInDB1
CREATE TABLE #TabNotInDB1 (name sysname)
INSERT INTO #TabNotInDB1
EXEC ('select name from '+@db2+'.dbo.sysobjects d1o '+
'where name in (select * from #TabToCheck) and '+
' d1o.type = ''U'' and not exists '+
'(select * from '+@db1+'.dbo.sysobjects d2o'+
' where d2o.type = ''U'' and d2o.name = d1o.name) order by name')
IF @@ROWCOUNT > 0
BEGIN
PRINT CHAR(10)+'The table(s) exist in '+@db2+', but do not exist in '+@db1+':'
SELECT * FROM #TabNotInDB1
END
DELETE FROM #TabToCheck WHERE name IN (SELECT * FROM #TabNotInDB1)
DROP TABLE #TabNotInDB1
-- Comparing structures of tables existing in both dbs
PRINT CHAR(10)+'Checking if there are tables existing in both databases having structural differences ...'+CHAR(10)
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name LIKE '#DiffStructure%')
DROP TABLE #DiffStructure
CREATE TABLE #DiffStructure (name sysname)
SET @sqlStr='
declare @TName1 sysname, @TName2 sysname, @CName1 sysname, @CName2 sysname,
@TypeName1 sysname, @TypeName2 sysname,
@CLen1 smallint, @CLen2 smallint, @Type1 sysname, @Type2 sysname, @PrevTName sysname
declare @DiffStructure bit
declare Diff cursor fast_forward for
select d1o.name, d2o.name, d1c.name, d2c.name, d1t.name, d2t.name,
d1c.length, d2c.length, d1c.type, d2c.type
from ('+@db1+'.dbo.sysobjects d1o
JOIN '+@db2+'.dbo.sysobjects d2o2 ON d1o.name = d2o2.name and d1o.type = ''U'' --only tables in both dbs
and d1o.name in (select * from #TabToCheck)
JOIN '+@db1+'.dbo.syscolumns d1c ON d1o.id = d1c.id
JOIN '+@db1+'.dbo.systypes d1t ON d1c.xusertype = d1t.xusertype)
FULL JOIN ('+@db2+'.dbo.sysobjects d2o
JOIN '+@db1+'.dbo.sysobjects d1o2 ON d1o2.name = d2o.name and d2o.type = ''U'' --only tables in both dbs
and d2o.name in (select * from #TabToCheck)
JOIN '+@db2+'.dbo.syscolumns d2c ON d2c.id = d2o.id
JOIN '+@db2+'.dbo.systypes d2t ON d2c.xusertype = d2t.xusertype)
ON d1o.name = d2o.name and d1c.name = d2c.name
WHERE (not exists
(select * from '+@db2+'.dbo.sysobjects d2o2
JOIN '+@db2+'.dbo.syscolumns d2c2 ON d2o2.id = d2c2.id
JOIN '+@db2+'.dbo.systypes d2t2 ON d2c2.xusertype = d2t2.xusertype
where d2o2.type = ''U''
and d2o2.name = d1o.name
and d2c2.name = d1c.name
and d2t2.name = d1t.name
and d2c2.length = d1c.length)
OR not exists
(select * from '+@db1+'.dbo.sysobjects d1o2
JOIN '+@db1+'.dbo.syscolumns d1c2 ON d1o2.id = d1c2.id
JOIN '+@db1+'.dbo.systypes d1t2 ON d1c2.xusertype = d1t2.xusertype
where d1o2.type = ''U''
and d1o2.name = d2o.name
and d1c2.name = d2c.name
and d1t2.name = d2t.name
and d1c2.length = d2c.length))
order by coalesce(d1o.name,d2o.name), d1c.name
open Diff
fetch next from Diff into @TName1, @TName2, @CName1, @CName2, @TypeName1, @TypeName2,
@CLen1, @CLen2, @Type1, @Type2
set @PrevTName = ''''
set @DiffStructure = 0
while @@fetch_status = 0
begin
if Coalesce(@TName1,@TName2) <> @PrevTName
begin
if @PrevTName <> '''' and @DiffStructure = 1
begin
insert into #DiffStructure values (@PrevTName)
set @DiffStructure = 0
end
set @PrevTName = Coalesce(@TName1,@TName2)
print @PrevTName
end
if @CName2 is null
print '' Column ''+RTRIM(@CName1)+'' not in '+@db2+'''
else
if @CName1 is null
print '' Column ''+RTRIM(@CName2)+'' not in '+@db1+'''
else
if @TypeName1 <> @TypeName2
print '' Column ''+RTRIM(@CName1)+'': in '+@db1+' - ''+RTRIM(@TypeName1)+'', in '+@db2+' - ''+RTRIM(@TypeName2)
else --the columns are not null(are in both dbs) and types are equal,then length are diff
print '' Column ''+RTRIM(@CName1)+'': in '+@db1+' - ''+RTRIM(@TypeName1)+''(''+
LTRIM(STR(CASE when @TypeName1=''nChar'' or @TypeName1 = ''nVarChar'' then @CLen1/2 else @CLen1 end))+
''), in '+@db2+' - ''+RTRIM(@TypeName2)+''(''+
LTRIM(STR(CASE when @TypeName1=''nChar'' or @TypeName1 = ''nVarChar'' then @CLen2/2 else @CLen2 end))+'')''
if @Type1 = @Type2
set @DiffStructure=@DiffStructure -- Do nothing. Cannot invert predicate
else
set @DiffStructure = 1
fetch next from Diff into @TName1, @TName2, @CName1, @CName2, @TypeName1, @TypeName2,
@CLen1, @CLen2, @Type1, @Type2
end
deallocate Diff
if @DiffStructure = 1
insert into #DiffStructure values (@PrevTName)
'
EXEC (@sqlStr)
IF (SELECT COUNT(*) FROM #DiffStructure) > 0
BEGIN
PRINT CHAR(10)+'The table(s) have the same name and different structure in the databases:'
SELECT DISTINCT * FROM #DiffStructure
DELETE FROM #TabToCheck WHERE name IN (SELECT * FROM #DiffStructure)
END
ELSE
PRINT CHAR(10)+'There are no tables with the same name and structural differences in the databases'+CHAR(10)+CHAR(10)
IF @OnlyStructure = 1
BEGIN
PRINT 'The option ''Only compare structures'' was specified. End of work.'
RETURN
END
EXEC ('declare @Name sysname select @Name=d1o.name
from '+@db1+'.dbo.sysobjects d1o, '+@db2+'.dbo.sysobjects d2o
where d1o.name = d2o.name and d1o.type = ''U'' and d2o.type = ''U''
and d1o.name not in (''dtproperties'')
and d1o.name in (select * from #TabToCheck)')
IF @@ROWCOUNT = 0
BEGIN
PRINT 'There are no tables with the same name and structure in the databases to compare. End of work.'
RETURN
END
END
No comments:
Post a Comment