Hi all,
Sql server 7
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.
waiting for reply.
TIA
AdilI'd just run selects from sysobjects and syscolumns, which are SQL Server's method of storing object definitions. Others on this forum will probably suggest selecting from the SCHEMA tables.|||Check LEFT OUTER JOIN in BOL, this should be very trivial (hint: ...no, t's too trivial ;) )|||hi
thnks for reply
can u tell me as to how i can select columns of a particular table using syscolumns
thansks once again|||Originally posted by aadil
hi
thnks for reply
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
where so.xtype='U'
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)'
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment