how to identify table name using a column name if we are not aware about the table name

Question

how to identify table name using a column name if we are not aware about the table name

Imagine if you have 10000 tables in Database

I am aware about the column name , but i dont know the exact table name .

Could you please advise is there a chance to find the table name , using the column name which you are aware about it already?

Answer 1

You can use INFORMATION_SCHEMA.COLUMNS instead of sys.columns

SELECT
    TABLE_CATALOG,TABLE_SCHEMA,TABLE_SCHEMA,COLUMN_NAME
FROM
    INFORMATION_SCHEMA.COLUMNS 
WHERE
    COLUMN_NAME LIKE '%MyColumn'

Answer 2

If this is for Microsoft SQL Server (you should add a sql-server tag to your questino in this case!), you can use this query:

SELECT
    ColumnName = c.Name,
    SchemaName = SCHEMA_NAME(t.schema_id),
    TableName = t.name
FROM 
    sys.columns c
INNER JOIN 
    sys.tables t ON t.object_id = c.object_id
WHERE
    c.name = 'your-column-name-here'