0

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?

1
  • 5
    Which DBMS are you using? You can query the metadata to find which tables have columns of that name. Feb 20, 2015 at 8:14

2 Answers 2

3

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'
1

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'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.