143

How can I get all the table names where the given column name exists? I want the names with "Like" in sql server. For example :-

  select TableNames where columnname like '%MyColumn%'

Please help me

  • 3
    **not duplicate** because this question calls for `LIKE` so is a broader request. The selected answer provides the column name as well to address the variability of column names that LIKE will return. – gordon Sep 15 '17 at 15:08

3 Answers3

314

Please try the below query. Use sys.columns to get the details :-

SELECT c.name AS ColName, t.name AS TableName
FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%MyCol%';
Ahmad
  • 69,608
  • 17
  • 111
  • 137
Naveen Chandra Tiwari
  • 5,055
  • 3
  • 20
  • 26
  • 8
    and to see schema name add SCHEMA_NAME(t.schema_id) – Reza ArabQaeni Sep 23 '17 at 14:10
  • 1
    Is there any way to select the name of the database in this query as well? – Kellen Stuart Oct 29 '18 at 17:40
  • @KolobCanyon use this DB_NAME() – Naveen Chandra Tiwari Oct 30 '18 at 16:16
  • 3
    @KolobCanyon this search works only on the database you are on since the sys.column table is a system table (or view not sure) that is created on every DB. If you want to search through different DBs, that would be a more complex process, probably would imply using a loop that iterates through the differents DBs. – DkAngelito Jan 10 '19 at 22:45
  • 1
    thank you so much for this. I've been coming back here frequently (for the past few months) for this query. Thought I'd leave a thank you comment haha! – jPhizzle Jun 27 '19 at 16:19
30

You could do this:

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%MyColumn%'
ORDER BY schema_name, table_name;

Reference:

Arion
  • 31,011
  • 10
  • 70
  • 88
20

Try Like This: For SQL SERVER 2008+

SELECT c.name AS ColName, t.name AS TableName
FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%MyColumnaName%'

Or 

SELECT COLUMN_NAME, TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE '%MyName%'

Or Something Like This:

SELECT name  
FROM sys.tables 
WHERE OBJECT_ID IN ( SELECT id 
              FROM syscolumns 
              WHERE name like '%COlName%' )
Dgan
  • 10,077
  • 1
  • 29
  • 51