Determing table name from Column names

Question

suppose if I know the column names, how do I determine which tables has those column names? In my database I have a lot of tables but I am not sure which tables contain those column names. It is not practically possible to open all the tables in the database? please help. I know the key column values of some tables but I do not know where exactly are those values corresponding to my id is defined. eg. I know address key ,shipmentkey etc but don't know in which table I would find the values of these. ? if I have address key 1, I should be able to find the address corresponding to 1. Is ther a way in sql server to find this out?

Answer 1

You can do the following

 select TABLE_NAME,COLUMN_NAME 
 from INFORMATION_SCHEMA.COLUMNS
 where COLUMN_NAME = 'YourColumnNameHere'

This will return all the tables(and views) where your column is used.

Answer 2

Finding the table(s) that have a specific column name is relatively simple:

SELECT t.name FROM sys.columns c 
JOIN sys.tables t ON c.object_id = t.object_id 
WHERE c.name = 'NAME'
ORDER BY t.name

That will not determine which table has which ID value, though. You would have to query the returned tables individually.