SQL column name search

Question

I'm using SQL Server Management Studio. I have a massive database and I want to search for column names that might be able to help me figure out what I need to join on.

Is there a tool or other method for doing that kind of a search? I have been looking for a way to do a column search but can't find one.

Answer 1

You have two options. Option 1 is to use the system views:

select
    t.name
    ,c.column_id
    ,c.name
    ,st.name
from
    sys.tables t
    join sys.columns c on (t.object_id = c.object_id)
    join sys.types st on (c.system_type_id = st.system_type_id)
where
    c.name = ''

Option 2 is a free add on from Red Gate Software called SQL Search.

Answer 2

I always turn to INFORMATION_SCHEMA views. For some reason this is now deprecated; I don't understand why.

SELECT TABLE_NAME,COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'whatever'