January 22, 2009 at 4:05 am
Hi,
How to find difference of column name between two tables.
Thanks
January 22, 2009 at 4:25 am
What exactly are you trying to find out ??
January 22, 2009 at 6:22 am
You can compare the column information in information_schema.columns. This does not cover all of the column information available, but you should be able to modify it. Notice that it checks exceptions between table1 and table2 and unions them with exceptions between table2 and table1 - this will get you the fields missing in either table.
This is not the most efficient solution - a full outer join would be faster, but the data sets should be small enough that it will not take long anyway.
[font="Courier New"]SELECT
Column_Name, Ordinal_Position, Column_Default, Is_Nullable, Data_Type, Character_Maximum_Length
, Character_Octet_Length, Numeric_Precision
FROM
Information_Schema.Columns
WHERE
Table_Schema = 'Schema1'
AND Table_Name = 'Table1'
EXCEPT
SELECT
Column_Name, Ordinal_Position, Column_Default, Is_Nullable, Data_Type, Character_Maximum_Length
, Character_Octet_Length, Numeric_Precision
FROM
Information_Schema.Columns
WHERE
Table_Schema = 'Schema2'
AND Table_Name = 'Table2'
UNION ALL
SELECT
Column_Name, Ordinal_Position, Column_Default, Is_Nullable, Data_Type, Character_Maximum_Length
, Character_Octet_Length, Numeric_Precision
FROM
Information_Schema.Columns
WHERE
Table_Schema = 'Schema2'
AND Table_Name = 'Table2'
EXCEPT
SELECT
Column_Name, Ordinal_Position, Column_Default, Is_Nullable, Data_Type, Character_Maximum_Length
, Character_Octet_Length, Numeric_Precision
FROM
Information_Schema.Columns
WHERE
Table_Schema = 'Schema1'
AND Table_Name = 'Table1'[/font]
January 22, 2009 at 7:25 am
Michael solution will work, but I always recommend going out and getting a tool that does this like ApexSQLDiff or RedGate SQLCompare. Long term it will be worth the money.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply