6

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.

  • If there's a problem with my question let me know what it is and I will gladly change it. – Jmaurier Jun 11 '15 at 14:19
  • Can't you look at the system catalogue? – Vérace Jun 11 '15 at 15:04
  • I could, I just didn't know how to do that or that it existed until I asked the question. Also if you haven't already looked at the line that Mike posted it's worth a look. – Jmaurier Jun 11 '15 at 18:17
  • 1
    Not a SQL Server person, but I do know that they all have their own system catalogues - or stores of metadata if you prefer. You can always use this metadata to find out lots about the system and its setup. Check out Codd's rules here, "Rule 4: Active online catalog based on the relational model: The system must support an online, inline, relational catalog that is accessible to authorized users by means of their regular query language."... – Vérace Jun 11 '15 at 19:45
9

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.

  • Newest version of that tool is awesome, big improvement. – Shawn Melton Jun 11 '15 at 14:26
1

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'

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.