How to compare two different tables attributes of two databases in a server?

use masterGO
if
( select count(*) from sysobjects where id = object_id(N’sp_CompareDbTables’) ) > 0 drop proc sp_CompareDbTables
GO
create proc sp_CompareDbTables@dbA_Name sysname
, @dbB_Name sysname
, @TableNameLike varchar(100) = null
– null = All Tables, ‘IBTA%’ = Tables like ‘IBTA%’
, @CompareOnlyDifference int = 1
– 1 = Compare only difference, 0 = Compare all
AS/*
——————————————
Purpose : Comparing Tables in two databases in a server
Returns Table Name along with number of fields from two databases
Exec Sample :
For Comparison only difference of tables :
exec sp_CompareDbTables ‘db_pcbank2000’, ‘db_pcbank2000back’
For Comparison of all tables :
exec sp_CompareDbTables ‘db_pcbank2000’, ‘db_pcbank2000back’, ‘%’, 0
For Comparison (only difference) of specific types of table like IBTA% :
exec sp_CompareDbTables ‘db_pcbank2000’, ‘db_pcbank2000back’, ‘IBTA%’
For Comparison of specific types of table like IBTA% :
exec sp_CompareDbTables ‘db_pcbank2000’, ‘db_pcbank2000back’, ‘IBTA%’, 0
——————————————
*/declare @vQuery nvarchar(4000)
set nocount on
if ( select count(*) from master..sysdatabases where dbid = db_id( @dbA_Name ) ) = 0 begin
raiserror ( ‘Database namely ‘‘%s’’ does not exist in the server %s’
, 16, 1, @dbA_Name, @@servername )
return ( 1 )
end
else if ( select count(*) from master..sysdatabases where dbid = db_id( @dbB_Name ) ) = 0 begin
raiserror ( ‘Database namely ‘‘%s’’ does not exist in the server %s’
, 16, 1, @dbB_Name, @@servername )
return ( 1 )
end
set @TableNameLike = isnull( @TableNameLike, ’’ )
set @TableNameLike = ( case @TableNameLike when ’’ then ’%’ else @TableNameLike end )
if ( select count(*) from tempdb..sysobjects where id = object_id(N’tempdb.._tempDbA’) ) > 0
drop table tempdb.._tempDbA
if ( select count(*) from tempdb..sysobjects where id = object_id(N’tempdb.._tempDbB’) ) > 0
drop table tempdb.._tempDbB
create table tempdb.._tempDbA( Name sysname )
create table tempdb.._tempDbB( Name sysname )

set @vQuery = N’use ‘ + @dbA_Name + ’
insert into tempdb.._tempDbA select Name from sysobjects
where OBJECTPROPERTY(id, N’‘IsUserTable’’) = 1
order by Name’
exec sp_executesql @vQuery

set @vQuery = N’use ‘ + @dbB_Name + ’
insert into tempdb.._tempDbB select Name from sysobjects
where OBJECTPROPERTY(id, N’‘IsUserTable’’) = 1
order by Name’

exec sp_executesql @vQuery

create table #Compare
(dbA_TableName sysname null, dbB_TableName sysname null, dbA_TableFields int, dbB_TableFields int)

insert into #Compare ( dbA_TableName, dbB_TableName )
select dbA_TableName = a.Name
, dbB_TableName = b.Name
from tempdb.._tempDbA a full outer join tempdb.._tempDbB b on a.Name = b.Name
order by isnull( a.Name, ’’) + isnull( b.Name, ’’ )
if ( select count(*) from tempdb..sysobjects where id = object_id(N’tempdb.._tempDbA’) ) > 0
drop table tempdb.._tempDbA
if ( select count(*) from tempdb..sysobjects where id = object_id(N’tempdb.._tempDbB’) ) > 0
drop table tempdb.._tempDbB

set @vQuery = N’update #Compare set dbA_TableFields
= ( select count(*) from ‘
+ @dbA_Name + ’..syscolumns where id = object_id (N’’’
+ @dbA_Name + ’..’’ + dbA_TableName ) )from #Compare where dbA_TableName is not null’
exec sp_executesql @vQuery

set @vQuery = N’update #Compare set dbB_TableFields
= ( select count(*) from ‘
+ @dbB_Name + ’..syscolumns where id = object_id (N’’’
+ @dbB_Name + ’..’’ + dbB_TableName ) )from #Compare where dbB_TableName is not null’
exec sp_executesql @vQuery

declare @Compare table
(TableName sysname not null, ExistInDbA int, ExistInDbB int, FieldsInDbA int, FieldsInDbB int)insert into @Compare
select TableName = isnull( dbA_TableName, dbB_TableName )
, ExistInDbA = ( case when dbA_TableName is null then 0 else 1 end )
, ExistInDbB = ( case when dbB_TableName is null then 0 else 1 end )
, FieldsInDbA = dbA_TableFields
, FieldsInDbB = dbB_TableFields
from #Compare
where isnull( dbA_TableName, dbB_TableName ) like @TableNameLike
and ( case when ( isnull( dbA_TableFields, 0 ) isnull( dbB_TableFields, 0 ) )
then 1 else 0 end ) >= @CompareOnlyDifference
set nocount off
select * from @Compare
GO