SQL help - find table with "somefieldId" as primary key?
How can I search my sql database for the table containing the field tiEntityId
. This field is listed in the stored procedure, but I can't figure out which table this ID is the primary key in? Any suggestions? I am currently reviewing stored procedure definitions for text references 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 knows what I need to use to find the table that contains this field, preferably the table in which that field is the Primary Key.
+2
a source to share
2 answers
If you are looking for primary keys containing a column with a given name (in SQL 2005+), here you are:
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
+1
a source to share