April 4, 2008 at 4:13 pm
Is there a script which will compare the structure of two tables. one table resides in sql 2005 and the other table resides on 2000 sql server.
Thanks
April 4, 2008 at 4:54 pm
Many ways to do this. Here is a simple one to get started
select *
into #a
from information_schema.columns a
where table_name = 'aaa'
select *
into #b
from information_schema.columns b -- add linked server name and db as needed
where table_name = 'bbb'
select
from #a a
left join #b b -- left/right/full outer as desired
on a.column_name = b.column_name
where a.data_type <> b.data_type -- or any of the other fields
April 7, 2008 at 1:01 am
[font="Verdana"]
also refer below URL
http://qa.sqlservercentral.com/scripts/Administration/62276/
Thanks,
Mahesh
[/font]
MH-09-AM-8694
April 8, 2008 at 9:11 am
Take a peek at this:
http://qa.sqlservercentral.com/scripts/Administration/61813/
Of course the main sproc that you are looking for in the article is
sp_table_validation
It will tell you if they are different but not what the particular schematic differences are.
September 8, 2010 at 3:20 pm
ksullivan (4/4/2008)
Many ways to do this. Here is a simple one to get started
select *
into #a
from information_schema.columns a
where table_name = 'aaa'
select *
into #b
from information_schema.columns b -- add linked server name and db as needed
where table_name = 'bbb'
select
from #a a
left join #b b -- left/right/full outer as desired
on a.column_name = b.column_name
where a.data_type <> b.data_type -- or any of the other fields
Hey, i tried and got this error
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'from'.
September 8, 2010 at 3:36 pm
Put a * or name some columns after the last select.
For example,
select * -- a.column_name or a.is_nullable etc
from #a a
left join #b b -- left/right/full outer as desired
on a.column_name = b.column_name
where a.data_type <> b.data_type -- or any of the other fields
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply