January 31, 2008 at 7:30 pm
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.
January 31, 2008 at 7:52 pm
Can you use the INFORMATION_SCHEMA view, or doesn't that work across databases?
January 31, 2008 at 8:00 pm
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!
February 6, 2008 at 3:42 am
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
James Horsley
Workflow Consulting Limited
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply