SQL help - find the table that has 'somefieldId' as the primary key?

Question

Hello All, How can I search my sql database for a table that contains a field 'tiEntityId'. This field is referenced in a stored procedure, but I am unable to identify which table this id is a primary key for? Any suggestions? I currently look through stored procedure definitions for references to text by using something like this

Declare @Search varchar(255)
SET @Search='[10.10.100.50]'

SELECT DISTINCT
o.name AS Object_Name,o.type_desc
FROM sys.sql_modules        m 
    INNER JOIN sys.objects  o ON m.object_id=o.object_id
WHERE m.definition Like '%'+@Search+'%'
ORDER BY 2,1

Any SQL guru's out there know what I need to use to find the table that contains the field, 'preferably the table where that field is the Primary Key.

Thanks so much for any tips. Cheers, ~ck in San Diego

Answer 1

You can do:

select table_name
from INFORMATION_SCHEMA.COLUMNS
where column_name = 'MyColumn'

Answer 2

If you're looking for primary keys that contain a column with a given name (in SQL 2005+), here you go:

select so.name as TableName,
       si.name as IndexName,
       sc.name as ColumnName
  from sys.indexes si
  join sys.index_columns sic
    on si.object_id = sic.object_id
   and si.index_id = sic.index_id
  join sys.columns sc
    on si.object_id = sc.object_id
   and sic.column_id = sc.column_id
  join sys.objects so
    on si.object_id = so.object_id
 where sc.name like '%ColumnName%'
   and si.is_primary_key = 1