August 15, 2008 at 2:05 am
H all,
Is there a way to find out any matches of columns between two tables in SQL. I would like to do a comparison and find all matching columns?
Thanks in advance
August 15, 2008 at 2:34 am
Hi,
Are you trying to match column names, datatypes, lengths?
I guess you could write a query that checks the sysobjects table for tables, and then joins onto syscolumns using the ID field, and then having some form of condition where you compare table column names with each other.
You might actually have to write a T-SQL script that moves subsets of data around doing compares, and then storing the HITS you are looking for.
Something like this:
select
a.name as [Table Name]
,b.name as [Column Name]
,c.name as [Data Type]
,c.length as [Length]
from sysobjects a
inner join syscolumns b on a.id = b.id
inner join systypes c on b.xusertype = c.xusertype
where a.xtype = 'U'
order by a.name
Regards,
Justin
August 15, 2008 at 6:46 am
Would suggest that you review
sys.system_columns, sys.columns and sys.all_columns in BOL and you should be able to write some T-SQL to extract the information you are seeking
or
use INFORMATION_SCHEMA.COLUMNS for example:
Using the AdventureWorks database and comparing the tables "ProductVendor" and "ProductReview"
SELECT Table_Name, Column_Name, Data_Type, Character_Maximum_Length FROM INFORMATION_SCHEMA.COLUMNS
WHERE Table_Name = 'ProductVendor' OR Table_Name = 'ProductReview' ORDER BY Data_Type will produce:
ProductVendor LastReceiptCost money NULL
ProductVendor UnitMeasureCode nchar 3
ProductReview Comments nvarchar 3850
ProductReview ReviewerName nvarchar 50
ProductReview EmailAddress nvarchar 50
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply