Best approach for Schema compare of 2 tables in same 2000 Database

  • I have 2 tables in the same database both supposed to be with the same schema. They normally are becuase I create the _compare tables with my scripts from production. However, I need to get the best approach and hopefully there is a script out there to solve this. For example, I have table A_compare(which is a temp table I have created) with 5 columns and I have my scripts or DTS package ready to truncate and populate the same exact table A that is already on the database, but before I can truncate or overwrite this table I need to make sure the schema is the same on table A or I need the entire process to halt and throw me an error and not allow either my proc or dts package to continue. Can anyone help please? Thanks.

  • Can you use the INFORMATION_SCHEMA view, or doesn't that work across databases?

  • Hi,

    Yes, I initally began trying by selecting at least the most important columns from the columns view. I think this just belongs in a cursor going through and matching each column for each table. There are only 14 tables in question here so I would create the 14 _compare table right away and I am just looking for a way to spin through them and compare the schema and if all is good, I can move on to the next step but if at any point it fails I need an error thrown to stop the process. Thanks for the speedy response!

  • What about something like

    SELECT

    *

    FROM

    (

    SELECT

    COLUMN_NAME,

    ORDINAL_POSITION,

    IS_NULLABLE,

    DATA_TYPE,

    Coalesce(COLUMN_DEFAULT,'') COLUMN_DEFAULT,

    Coalesce(CHARACTER_MAXIMUM_LENGTH,-1) CHARACTER_MAXIMUM_LENGTH,

    Coalesce(NUMERIC_PRECISION,-1) NUMERIC_PRECISION,

    Coalesce(NUMERIC_PRECISION_RADIX,-1) NUMERIC_PRECISION_RADIX,

    Coalesce(NUMERIC_SCALE,-1) NUMERIC_SCALE,

    Coalesce(COLLATION_NAME,'') COLLATION_NAME

    FROM

    INFORMATION_SCHEMA.COLUMNS

    WHERE

    TABLE_NAME='TABLEA'

    ) TableAStruc

    FULL OUTER JOIN

    (

    SELECT

    COLUMN_NAME,

    ORDINAL_POSITION,

    IS_NULLABLE,

    DATA_TYPE,

    Coalesce(COLUMN_DEFAULT,'') COLUMN_DEFAULT,

    Coalesce(CHARACTER_MAXIMUM_LENGTH,-1) CHARACTER_MAXIMUM_LENGTH,

    Coalesce(NUMERIC_PRECISION,-1) NUMERIC_PRECISION,

    Coalesce(NUMERIC_PRECISION_RADIX,-1) NUMERIC_PRECISION_RADIX,

    Coalesce(NUMERIC_SCALE,-1) NUMERIC_SCALE,

    Coalesce(COLLATION_NAME,'') COLLATION_NAME

    FROM

    INFORMATION_SCHEMA.COLUMNS

    WHERE

    TABLE_NAME='TABLEB'

    ) TableBStruc

    ON TableAStruc.ORDINAL_POSITION=TableBStruc.ORDINAL_POSITION

    WHERE

    TableAStruc.ORDINAL_POSITION IS Null -- Missing in A

    OR

    TableBStruc.ORDINAL_POSITION IS Null -- Missing in B

    OR

    (

    TableAStruc.ORDINAL_POSITION!=TableBStruc.ORDINAL_POSITION

    OR

    TableAStruc.IS_NULLABLE!=TableBStruc.IS_NULLABLE

    OR

    TableAStruc.DATA_TYPE!=TableBStruc.DATA_TYPE

    OR

    TableAStruc.COLUMN_DEFAULT!=TableBStruc.COLUMN_DEFAULT

    OR

    TableAStruc.CHARACTER_MAXIMUM_LENGTH!=TableBStruc.CHARACTER_MAXIMUM_LENGTH

    OR

    TableAStruc.NUMERIC_PRECISION!=TableBStruc.NUMERIC_PRECISION

    OR

    TableAStruc.NUMERIC_PRECISION_RADIX!=TableBStruc.NUMERIC_PRECISION_RADIX

    OR

    TableAStruc.NUMERIC_SCALE!=TableBStruc.NUMERIC_SCALE

    OR

    TableAStruc.COLLATION_NAME!=TableBStruc.COLLATION_NAME

    )

    You might need to add a few more of the columns from INFORMATION_SCHEMA.COLUMNS for you needs - but basically if this finds any rows there is a mismatch

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply