Find all tables containing column with specified n

Question

Is it possible to query for table names which contain columns being

LIKE '%myName%'

?

Answer 1

Here's a working solution for a Sybase database

select 
  t.table_name, 
  c.column_name 
from 
  systab as t key join systabcol as c 
where 
   c.column_name = 'MyColumnName'

Answer 2

I don't know why so many of you suggesting Joining with sys.table with sys.columns you can simply use below code:

Select object_name(object_id) as TableName,* from SYS.columns where name LIKE '%MyName%'

or

If you want schema name as well:

Select * from  INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME LIKE '%MyName%'

Answer 3

USE AdventureWorks

GO

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 '%EmployeeID%'
ORDER BY schema_name, table_name; 

It is from Pinal Sir Blog

Answer 4

Following query will give you the exact table names of the database having field name like '%myName'.

SELECT distinct(TABLE_NAME)
  FROM INFORMATION_SCHEMA.COLUMNS    
 WHERE COLUMN_NAME LIKE '%myName%'

Answer 5

I used this for the same purpose and it worked:

  select * from INFORMATION_SCHEMA.COLUMNS
  where TABLE_CATALOG= 'theDatabase'
  and COLUMN_NAME like 'theCol%'

Answer 6

You can use [INFORMATION_SCHEMA].[COLUMNS] table to find columns Such as

Select * From [INFORMATION_SCHEMA].[COLUMNS] Where COLUMN_NAME like '%Column%'

For getting Table and Column information for given SQL statement visit http://www.w3hattrick.com/2016/05/getting-table-and-column-information.html