5

What's the quickest way to find what table a field is in? I just started at a new company and I'm not yet familiar with their DB's schema. I frequently have a metric or dimension that I need to search for and it seems very time consuming as they have a very large DB.

I know this is a vague question and there is no best answer, but I'm looking for best practices, tips, and tricks that people have learned over the years which I might be able to leverage in this case.

2 Answers2

6

You can use sys.columns for that:

SELECT 
    OBJECT_NAME(c.object_id)
FROM sys.columns c
WHERE c.name = <column_name>
Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
1

I normally use the following query when I’m searching for a field. 

SELECT * FROM information_schema.COLUMNS c WHERE c.COLUMN_NAME LIKE 'search_field%'

Make sure you have the DB you’re interested in selected.

Then, once you find the table you’re looking for you can create a DB diagram and ‘show related fields’ to see how to join that table on what you already have.

  • 6
    [The case against INFORMATION_SCHEMA views](https://sqlblog.org/2011/11/03/the-case-against-information_schema-views) – Felix Pamittan Apr 13 '16 at 01:51
  • 1
    Hey Felix, this is a large article. In short, is there an issue with Jessica and prdp's answer? –  Apr 13 '16 at 01:54
  • 2
    @Victor If you're just searching for which tables a column is used, this is okay. But if you're going to need some additional information, `INFORATION_SCHEMA`does not provide them all. – Felix Pamittan Apr 13 '16 at 01:58