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://www.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://www.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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy