How to find a table with a primary key with a certain column name in Microsoft SQL?

Question

In Microsoft SQL, if I want to get information about where a column with a certain column name appears in my database, I can run this query:

select * from information_schema.columns where column_name = 'XXXXX';

This query returns a wealth of information about the instances where a column with the name appears, but I don't see anything about referential constraints.

I've tried some other queries below, but none seem to yield the information I want:

select * from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

I want to write a query that would do something like this:

select table_schema, table_name from INFORMATION_SCHEMA.COLUMNS
where column_name = 'XXXXX' and IsPrimaryKey = 1;

Edit: It was suggested that this question duplicates a question about finding the primary key of a given table. This question is related, but different because I am starting out knowing the name of a column (which may occur in many tables) and I want to be able to tell if there is a table with a primary key column with the same name.

If your PRIMARY_KEY is the clustered key, then, it is the table. — DVT, Dec 06 '16 at 17:18
http://stackoverflow.com/questions/3930338/sql-server-get-table-primary-key-using-sql-query — SQLChao, Dec 06 '16 at 17:22
http://stackoverflow.com/questions/95967/how-do-you-list-the-primary-key-of-a-sql-server-table — Neo, Dec 06 '16 at 17:29

score 1 · Answer 1 · answered Dec 06 '16 at 17:23

1

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + CONSTRAINT_NAME), 'IsPrimaryKey') = 1
AND COLUMN_NAME = 'ColumnName' AND TABLE_SCHEMA = 'SchemaName'

answered Dec 06 '16 at 17:23

3N1GM4

3,372
3
19
40

Not sure why this was downvoted, but it works. I would change `table_name` to `*` and remove the `and table_schema ...` not sure why that was there. +1 – SqlZim Dec 06 '16 at 19:00

SMM · Answer 2 · 2016-12-06T21:02:37.030

SELECT
    s.name AS schema_name,
    o.name AS table_name
    --, *
FROM sys.objects o
    join sys.schemas s on s.schema_id = o.schema_id
    join sys.indexes i on i.object_id = o.object_id
    join sys.index_columns ic  ON i.object_id = ic.object_id AND i.index_id = ic.index_id
    join sys.columns c ON ic.object_id = c.object_id AND c.column_id = ic.column_id
WHERE i.is_primary_key = 1
    and i.name = 'XXXXX';

EDIT:

I wrote the first way to show that PKs aren't always single column but forgot to note that and remove the extra rows. Here is the version that returns a single result.

SELECT
    s.name AS schema_name,
    o.name AS table_name
    --, *
FROM sys.objects o
    join sys.schemas s on s.schema_id = o.schema_id
    join sys.indexes i on i.object_id = o.object_id
    --join sys.index_columns ic  ON i.object_id = ic.object_id AND i.index_id = ic.index_id
    --join sys.columns c ON ic.object_id = c.object_id AND c.column_id = ic.column_id
WHERE i.is_primary_key = 1
    and i.name = 'XXXXX';

EDIT 2

The original question was a little unclear as pointed out by SqlZim. In case you want to ask by column name I have added that option. WARNING: This will return multiple tables if you have tables with the same column name as PK. This is most likely in larger databases and many compound keys (e.g. a financial system where something like FinancialYear is often part of many compound keys).

SELECT
    s.name AS schema_name,
    o.name AS table_name
    --, *
FROM sys.objects o
    join sys.schemas s on s.schema_id = o.schema_id
    join sys.indexes i on i.object_id = o.object_id
    join sys.index_columns ic  ON i.object_id = ic.object_id AND i.index_id = ic.index_id
    join sys.columns c ON ic.object_id = c.object_id AND c.column_id = ic.column_id
WHERE i.is_primary_key = 1
    and c.name = 'PROGRAM_YEAR';

To search by column name it should be `c.name = ...` not `i.name = ...` which means you do need those last two joins. — SqlZim, Dec 06 '16 at 20:01
The question in the title is how to find the table based on the primary key name. On the other hand his "something like" query indicates column name. I will add an option. — SMM, Dec 06 '16 at 20:56

How to find a table with a primary key with a certain column name in Microsoft SQL?

2 Answers2