Getting table name from column name
If you know the name of one column you want to find from which table that is originated, to retrieve the table name you can use following TSQL:
SELECT CASE (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS AS c2 WHERE c2.TABLE_NAME = c1.TABLE_NAME AND c2.COLUMN_NAME < c1.COLUMN_NAME)
WHEN 0 THEN c1.TABLE_NAME
ELSE ''
END AS TABLE_NAME,
c1.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS AS c1
ORDER BY c1.TABLE_NAME,
c1.COLUMN_NAME
I believe using Information_Schema views are best to get optimum values than directly querying the system tables in SQL Server.
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.