1

I have a weird situation. I have a case where I know a primary key column, but not the table it's in.

Anyone know a quick 'n easy way of finding that info? i was hoping that sp_pkeys could do that, but it doesn't let me use the column as a parameter.

Edit: This is NOT the same as looking for tables with a column (I already know how to do that). I'm looking for tables with a specific primary key (which is a subset of the "duplicate question").

Ray K.
  • 2,431
  • 3
  • 25
  • 39
  • Possible duplicate of [Find all tables containing column with specified name](http://stackoverflow.com/questions/4849652/find-all-tables-containing-column-with-specified-name) – Juan Carlos Oropeza Jul 20 '16 at 16:05
  • No, not the same question. I already know how to find tables with the specific column; I'm looking for a table with a specific primary key. – Ray K. Jul 20 '16 at 16:12

2 Answers2

2
SELECT COLUMN_NAME, TABLE_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + CONSTRAINT_NAME), 'IsPrimaryKey') = 1
AND COLUMN_NAME = 'yourColumnName'
S3S
  • 24,809
  • 5
  • 26
  • 45
1

Here is another way of doing this.

select OBJECT_NAME(c.Object_ID)
from sys.columns c
join sys.indexes i on c.object_id = i.object_id 
where i.is_primary_key = 1
    and c.name = 'YourColumn'
Sean Lange
  • 33,028
  • 3
  • 25
  • 40
  • Didn't work for me -- to test it out, I tried running sp_pkeys with the results, and it gave me answers where it gave me a PK with a different column. Sorry. – Ray K. Jul 20 '16 at 16:18
  • Maybe you have tables with the same name in different schemas. Oh well...looks like you have an answer that works for you. – Sean Lange Jul 20 '16 at 16:22
  • Maybe. Since I know you from SSC, I'm biased, and wanted to mark yours as the answer, but c'est la vie. :-) Oh well. – Ray K. Jul 20 '16 at 16:24
  • 1
    Nah. You should only be biased to get the best possible answer. Which in this case is not mine. Thanks for the thought though. :) – Sean Lange Jul 20 '16 at 16:29