You are here:Home » tsql » Script to Compare Table Schema

Script to Compare Table Schema

-- Works only in SQL SERVER 2005, ( Does not work in SQL Server 2000)

-- exec USP_CompareSchema 'Mirror4', 'Mirror3', 'Mirror2'

CREATE proc USP_CompareSchema
@db1 sysname ,
@db2 sysname,
@db3 sysname -- This is where your result table will be created.
as
declare @sql varchar(8000)
declare @result varchar(8000)
declare @table_name varchar(100)
declare @Nomismatchsql varchar(100)

select @table_name = @db3+'.dbo.SCHEMAVALIDATIONRESULTS_'+ convert(varchar(8),getdate(),112)+'_' + substring ( convert(varchar(20),getdate(),13) , 13 , 2)+ '_'+substring ( convert(varchar(20),getdate(),13) , 16 , 2)

set @sql = 'select * into '+@table_name+' from
(
select C.name '+'''Table_Name'''+', A.name '+'''Column_Name'''+', B.name '+'''Data_Type'''+',
case when A.length = '+'''-1'''+' then '+'''Max'''+' else convert(varchar(50), A.length) end '+'''Column_Length'''+','+'''' +@db1+''''+' '+'''DATABASE NAME'''+' from '+@db1+'.dbo.Syscolumns A, '+@db1+'.dbo.Systypes B, '+@db1+'.dbo.sysobjects C
where A.xusertype = B.xusertype and A.id = C.id and C.xtype = '+'''u'''+' and c.id <> 645577338

except

select C.name '+'''Table_Name'''+', A.name '+'''Column_Name'''+', B.name '+'''Data_Type'''+',
case when A.length = '+'''-1'''+' then '+'''Max'''+' else convert(varchar(50), A.length) end '+'''Column_Length'''+','+ ''''+@db1+''''+' '+'''DATABASE NAME'''+' from '+@db2+'.dbo.Syscolumns A, '+@db2+'.dbo.Systypes B, '+@db1+'.dbo.sysobjects C
where A.xusertype = B.xusertype and A.id = C.id and C.xtype = '+'''u'''+' and c.id <> 645577338 ) X

Insert into '+@table_name+' select * from
(
select C.name '+'''Table_Name'''+', A.name '+'''Column_Name'''+', B.name '+'''Data_Type'''+',
case when A.length = '+'''-1'''+' then '+'''Max'''+' else convert(varchar(50), A.length) end '+'''Column_Length'''+','+'''' +@db2+''''+' '+'''DATABASE NAME'''+' from '+@db2+'.dbo.Syscolumns A, '+@db2+'.dbo.Systypes B, '+@db2+'.dbo.sysobjects C
where A.xusertype = B.xusertype and A.id = C.id and C.xtype = '+'''u'''+' and c.id <> 645577338

except

select C.name '+'''Table_Name'''+', A.name '+'''Column_Name'''+', B.name '+'''Data_Type'''+',
case when A.length = '+'''-1'''+' then '+'''Max'''+' else convert(varchar(50), A.length) end '+'''Column_Length'''+','+ ''''+@db2+''''+' '+'''DATABASE NAME'''+' from '+@db1+'.dbo.Syscolumns A, '+@db1+'.dbo.Systypes B, '+@db1+'.dbo.sysobjects C
where A.xusertype = B.xusertype and A.id = C.id and C.xtype = '+'''u'''+' and c.id <> 645577338 ) Y'

exec (@sql)

select 'Results are stored in Table '+@Table_name 'Name of Results Table'

set @result = 'select * from '+@Table_name
exec (@result)