How to determine which columns are shared between two tables?

Question

Very new to SQL Sever here... I understand the concept of joining tables, etc. but what is the easiest way to determine which columns are shared?

Say for instance we have Table 1 and Table 2, assume table 1 has over 100 columns as does table 2, but they only have 1 column in common.

Is there a simple way to check to see what column / if any are shared without annoyingly going in and checking?

Pretty trivial question but very useful. Thanks

Answer 1

You can find data like this in the INFORMATION_SCHEMA tables. Technically those are more standardized than the sys views. (See this question.)

Here's a query you could use:

select A.COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS A
join INFORMATION_SCHEMA.COLUMNS B
  on A.COLUMN_NAME = B.COLUMN_NAME
where A.TABLE_NAME = 'table1'
  and B.TABLE_NAME = 'table2'

If you need to specify the schema to avoid name collisions, add A.TABLE_SCHEMA = 'dbo' etc to the where clause.

Answer 2

This could well indicate a fundamental design issue but to find column names shared by both tables a couple of options would be

SELECT name 
FROM sys.columns 
WHERE object_id IN (object_id('dbo.Table1'),
                    object_id('dbo.Table2'))
GROUP BY name
HAVING COUNT(*) = 2

Or

SELECT name 
FROM sys.columns 
WHERE object_id = object_id('dbo.Table1')
INTERSECT
SELECT name 
FROM sys.columns 
WHERE object_id = object_id('dbo.Table2')

Answer 3

Use the INFORMATION_SCHEMA.COLUMNS like this:

IF OBJECT_ID('Table1') IS NOT NULL DROP TABLE Table1
IF OBJECT_ID('Table2') IS NOT NULL DROP TABLE Table2
GO
CREATE TABLE Table1 (
    a INT
  , b INT
  , c INT
  , d INT
  , e INT
  , f INT
)

CREATE TABLE Table2 (
    c INT
  , d INT
  , e INT
  , f INT
  , g INT
  , h INT
  , i INT
)

GO

SELECT t1.COLUMN_NAME 
FROM        INFORMATION_SCHEMA.COLUMNS AS t1 
INNER JOIN  INFORMATION_SCHEMA.COLUMNS AS t2 ON t1.COLUMN_NAME = t2.COLUMN_NAME 
WHERE t1.TABLE_NAME = 'Table1' AND t2.TABLE_NAME = 'Table2'

-- OUTPUT

COLUMN_NAME
c
d
e
f

Answer 4

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'Table1'

intersect

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'Table2'

Answer 5

Here is a handy query you can use to list out columns in a table:

SELECT c.name ColumnName
FROM sys.columns c INNER JOIN
     sys.tables t ON c.object_id = t.object_id 
WHERE t.name = 'something'

And here is a JOIN you could use to find common column names:

SELECT * 
FROM  (SELECT c.name ColumnName
        FROM sys.columns c INNER JOIN
             sys.tables t ON c.object_id = t.object_id 
        WHERE t.name = 'table1'
      )t1
JOIN (SELECT c.name ColumnName
        FROM sys.columns c INNER JOIN
             sys.tables t ON c.object_id = t.object_id 
        WHERE t.name = 'table2'
     )t2
ON t1.ColumnName = t2.ColumnName

Answer 6

To know if you have similar columns could potentially be trickier than the other solutions suggest. We might think that two columns are the same because they share the same name but in reality when you work in a large database with more than one person creating, removing, and/or changing the data structure inconsistencies can happen.

The more parameters we check for likeness the more confident we can be that our columns are similar without manual inspection of raw data.

1. First, I suggest you run a query to understand the parameters of a given column.

SELECT 
    *
FROM 
    DATABASENAME.INFORMATION_SCHEMA.COLUMNS
WHERE 
    TABLE_NAME = N'TABLE1'

This will return several columns of meta data on the columns in the table. Some of the meta data I found interesting for uniqueness included...

2. In my case I have identified the column attributes of COLUMN_NAME, IS_NULLABLE, AND DATA_TYPE to determine if my columns truly match.

SELECT 
    DISTINCT A.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS A
    LEFT join INFORMATION_SCHEMA.COLUMNS B 
        ON A.COLUMN_NAME = B.COLUMN_NAME 
        AND A.DATA_TYPE = B.DATA_TYPE
        AND A.IS_NULLABLE = B.IS_NULLABLE
WHERE 
    A.TABLE_NAME = N'TABLE1'
    AND B.TABLE_NAME = N'TABLE2'

3. Concept Check... Maybe if when we JOIN using only COLUMN_NAME there are 10 matching columns. Maybe when we JOIN using COLUMN_NAME AND DATA_TYPE there are 7 matching columns. Maybe when we use all three conditions as in the example above there are 4 matching columns. Does it mean you can only JOIN on 4 matching columns...absolutely not. What it does mean is you will need to consider how to craft error handling and casting depending on how you intend to JOIN the tables. Point being is be careful performing JOIN on INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME as your results may be far from intended.

Answer 7

Use following query (name used to show coman columns list)

select name from syscolumns s1 where id = object_id('table1') and            exists(select 1 from syscolumns s2 where s2.name = s1.name and s2.id = object_id('table2'))

Answer 8

IF OBJECT_ID('tempdb..#Table1') IS NOT NULL DROP TABLE #Table1
IF OBJECT_ID('tempdb..#Table2') IS NOT NULL DROP TABLE #Table2


SELECT      COLUMN_NAME AS 'ColumnName'
            ,TABLE_NAME AS  'TableName'
INTO        #Table1
FROM        INFORMATION_SCHEMA.COLUMNS
WHERE       TABLE_NAME = 'TABLE_NAME1'
ORDER BY    TableName
            ,ColumnName;

SELECT      COLUMN_NAME AS 'ColumnName'
            ,TABLE_NAME AS  'TableName'
INTO        #Table2
FROM        INFORMATION_SCHEMA.COLUMNS
WHERE       TABLE_NAME = 'TABLE_NAME2'
ORDER BY    TableName
            ,ColumnName;

SELECT #Table1.ColumnName
FROM #Table1
JOIN #Table2 ON #Table1.ColumnName = #Table2.ColumnName