August 2, 2012 at 7:19 am
I have two tables say A and B.
Suppose A table has 19 columns and B table has 15 columns.
I want to perform join on the table and i know that the column name are same on which the join has to be applied but unfortunately i don't know the column name .
It would be a tedious job on my par to go and see each column name in table A with that of table B and then match them.
I would like to have a query which would perform the above mention task for me
e.g. A(a,b,.......k.........,s) //a,b,k,s etc are all column name
B(q,w................k....r) //q,w,k,r etc are all column name.
I want a query which would return me 'k'(as it is common in both the table)
August 2, 2012 at 7:25 am
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Table A'
INTERSECT
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Table B';
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 2, 2012 at 9:42 am
Koen Verbeeck (8/2/2012)
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Table A'
INTERSECT
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Table B';
You can also use a INNER JOIN instead of INTERSECT to accomplish the same thing. Adds a bit more bulk to the query though.
SELECT DISTINCT x.COLUMN_NAME
FROM
(SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Table A')x
INNER JOIN (SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Table B'
)y ON x.COLUMN_NAME = y.COLUMN_NAME
August 2, 2012 at 4:36 pm
Kirby1367 (8/2/2012)
Koen Verbeeck (8/2/2012)
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Table A'
INTERSECT
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Table B';
You can also use a INNER JOIN instead of INTERSECT to accomplish the same thing. Adds a bit more bulk to the query though.
SELECT DISTINCT x.COLUMN_NAME
FROM
(SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Table A')x
INNER JOIN (SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Table B'
)y ON x.COLUMN_NAME = y.COLUMN_NAME
Both Good solutions but don't take the following into consideration:
1) What if there are multiple schemas?
2) What if there is more than one matching column?
This query will address multiple schema's and will pick the first matching column
DECLARE @table1 varchar(100) = 'orig_tblSite',
@table2 varchar(100) = 'orig_tblCustVend'
;WITH y AS
(
SELECT COLUMN_NAME,COUNT(COLUMN_NAME) [ttl]
FROM
(
SELECTTABLE_SCHEMA,TABLE_NAME,COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
) x
GROUP BY COLUMN_NAME
HAVING COUNT(COLUMN_NAME)>1
),
z AS
(
SELECT isc.TABLE_NAME,isc.COLUMN_NAME,isc.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS isc
JOIN yON isc.COLUMN_NAME=y.COLUMN_NAME
AND isc.TABLE_NAME IN (@table1,@table2)
)
SELECT TOP 1 COLUMN_NAME
FROM z
ORDER BY ORDINAL_POSITION
GO
And, if you replace
SELECT TOP 1 COLUMN_NAME
FROM z
ORDER BY ORDINAL_POSITION
GO
With this:
SELECT * FROM z
ORDER BY COLUMN_NAME,TABLE_NAME
You will get this:
TABLE_NAME COLUMN_NAME ORDINAL_POSITION
----------------------- ---------------------------------
orig_tblCustVend Address 4
orig_tblSite Address 6
orig_tblCustVend Address 2 5
orig_tblSite Address 2 7
orig_tblCustVend City 6
orig_tblSite City 8
orig_tblCustVend County 8
orig_tblSite County 12
orig_tblCustVend Name 2
orig_tblSite Name 3
-- Itzik Ben-Gan 2001
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply