How to get columns that are repeating in different tables in a database inside SQL Server

Question

I've a database with huge number of tables. From these tables I want to list out all the column names that are appearing in more than one table along with the their table names.

I tried google search to find any suitable article that can explain how to achieve the results that I've described in the problem section.

No code snippets

No error messages.

Answer 1

This will return the column names and the table names:

select distinct 
TABLE_NAME, 
COLUMN_NAME 
from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME in(
    select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
    group by COLUMN_NAME
    having count(*) > 1
)
order by TABLE_NAME, COLUMN_NAME

Answer 2

There are a lot of results and snippets that show how to get all column names in a database. It's a single query to sys.columns, eg :

select Name
from sys.columns

The same SQL queries that return multiple occurences in any table can be used here too, eg :

select Name,count(*)
from sys.columns
group by Name
having count(*)>1

Will return column names that occur more than once.

You can get the column and table names without joining by using COUNT() with OVER :

with t as (
    select name,
           object_name(object_id) as TableName,
           count(*) over (partition by name) cnt
    from sys.columns
)
select * 
from t
where cnt >1

Answer 3

The query below will return the column name along with the number of its occurences in the current database tables:

SELECT c.NAME AS 'ColumnName', COUNT(*) AS Occurences
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
GROUP BY c.NAME
HAVING COUNT(*) > 1

This query below will help to identify the specific column name is placed in which tables:

-- Find column name in all the tables
SELECT c.NAME AS 'ColumnName', t.NAME AS 'TableName'
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.NAME LIKE '%columnname%' -- to be searchable column name
ORDER BY TableName, ColumnName;