i have two databases best and books.Most of the tables present in best are in books except some.
i have been given a task to list out the tables and columns of which are present in best database and not in books database.
Pls suggest me the simplest and quickest way to do this.
this is very urgent.
can u tell me as to how i can select columns of a particular table using syscolumns
thansks once again
You could use linked servers. Query below will return list of tables from server1 if these are no the same column(s) on server2. Checking for object owner also is included.
select 'User table ['+su.name+'.'+so.name+'] does not have column ['+sc.name+']'
from server1.dbo.sysobjects so
join server1.dbo.syscolumns sc on sc.id=so.id
join server1.dbo.sysusers su on su.uid=so.uid
and exists(select 'ok' from server2.dbo.sysobjects r
join server2.dbo.sysusers sur on sur.uid=r.uid and sur.name=su.name
where xtype='U' and r.name=so.name)
and not exists(select 'ok' from server2.dbo.sysobjects sor
join server2.dbo.sysusers sul on sul.uid=sor.uid and sul.name=su.name
join server2.dbo.syscolumns scr on scr.id=sor.id
where sor.xtype='U' and sor.name=so.name and scr.name=sc.name)'