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

Compare Tables?

Hello. I have two copies of a database (one older, one newer). I
would like to compare them and take the records that do not appear in
both dbs and copy them to the newer one.
As much as I would like to manually go through, row by row, is there
an easier way'
Using:
- SQL 7
- Win NT
Many thanks!
JDThere's a 3rd party product called SQLCompare...
--
HTH
Ryan Waight, MCDBA, MCSE
"JD" <whatchoogot@.hotmail.com> wrote in message
news:b809817a.0311120722.5bcbac2f@.posting.google.com...
> Hello. I have two copies of a database (one older, one newer). I
> would like to compare them and take the records that do not appear in
> both dbs and copy them to the newer one.
> As much as I would like to manually go through, row by row, is there
> an easier way'
> Using:
> - SQL 7
> - Win NT
> Many thanks!
> JD|||JD,
I have used this product it great but becareful because
it will lock the table during comparing. It best that
you don't do this in production server.
Take Care,
Rachan
>--Original Message--
>There's a 3rd party product called SQLCompare...
>--
>HTH
>Ryan Waight, MCDBA, MCSE
>"JD" <whatchoogot@.hotmail.com> wrote in message
>news:b809817a.0311120722.5bcbac2f@.posting.google.com...
>> Hello. I have two copies of a database (one older,
one newer). I
>> would like to compare them and take the records that
do not appear in
>> both dbs and copy them to the newer one.
>> As much as I would like to manually go through, row by
row, is there
>> an easier way'
>> Using:
>> - SQL 7
>> - Win NT
>> Many thanks!
>> JD
>
>.
>|||Also a 3rd party tool called DB Ghost at www.dbghost.com
Thanks,
Darren Fuller MCSE
>--Original Message--
>Hello. I have two copies of a database (one older, one
newer). I
>would like to compare them and take the records that do
not appear in
>both dbs and copy them to the newer one.
>As much as I would like to manually go through, row by
row, is there
>an easier way'
>Using:
>- SQL 7
>- Win NT
>Many thanks!
>JD
>.
>