Comparing order of fields in two copies of sql server database

I have a need to look at 2 copies of the same database structure and
check certain tables to make sure the fields are in the same order.
IE, if someone adds a field to table A in Database 1 in position 5,
and then adds the same field to Table A in Database 2 in position 10,
the two databases have all the same fields, but not in the same order.
Is there a way to check for this and change the order of the fields
in one database so it is the same as the other?
Thanks in advance for your help.
Carol
carol.cooper@.comcast.netthis is just an idea. If the 2 tables are from 2 DBs then you might need to
get the actual object ID of the table in that DB to replace object_id
select name from DB1..syscolumns where id = object_id('table A') order by
colid
join
select name from DB2..syscolumns where id = object_id('table B') order by
colid
"Carol Cooper" <carol.cooper@.comcast.net> wrote in message
news:9b2ed4c6.0307291339.30f3b6b2@.posting.google.com...
> I have a need to look at 2 copies of the same database structure and
> check certain tables to make sure the fields are in the same order.
> IE, if someone adds a field to table A in Database 1 in position 5,
> and then adds the same field to Table A in Database 2 in position 10,
> the two databases have all the same fields, but not in the same order.
> Is there a way to check for this and change the order of the fields
> in one database so it is the same as the other?
> Thanks in advance for your help.
> Carol
> carol.cooper@.comcast.net|||Carol,
You can compare the two tables by querying the information schema views on
each database, assuming they are on the same server. You could use a full
outer join and filter out matches. Here's an example:
SELECT
c1.table_name
, c1.column_name
, c1.ordinal_position
, c2.table_name
, c2.column_name
, c2.ordinal_position
FROM Database1.INFORMATION_SCHEMA.COLUMNS c1
FULL JOIN Database2.INFORMATION_SCHEMA.COLUMNS c2
ON c1.TABLE_NAME = c2.TABLE_NAME
AND c1.COLUMN_NAME = c2.COLUMN_NAME
AND c1.ORDINAL_POSITION = c2.ORDINAL_POSITION
WHERE
(
c1.TABLE_NAME IS NULL
OR
c2.TABLE_NAME IS NULL
OR
c1.COLUMN_NAME IS NULL
OR
c2.COLUMN_NAME IS NULL
OR
c1.ORDINAL_POSITION IS NULL
OR
c2.ORDINAL_POSITION IS NULL
)
When you find a difference, you'll need to fix the table using ALTER TABLE
commands. If you decide to use the scripts from Enterprise Manager's Design
Table dialog, make sure you read them carefully first. No matter what you
do, back up youir data first and figure out a rollback plan just in case.
Ron
--
Ron Talmage
SQL Server MVP
"Carol Cooper" <carol.cooper@.comcast.net> wrote in message
news:9b2ed4c6.0307291339.30f3b6b2@.posting.google.com...
> I have a need to look at 2 copies of the same database structure and
> check certain tables to make sure the fields are in the same order.
> IE, if someone adds a field to table A in Database 1 in position 5,
> and then adds the same field to Table A in Database 2 in position 10,
> the two databases have all the same fields, but not in the same order.
> Is there a way to check for this and change the order of the fields
> in one database so it is the same as the other?
> Thanks in advance for your help.
> Carol
> carol.cooper@.comcast.net