To get if Primary key Columns is referred as foreign key or not

Question

I need to know how to find a Primary Key in a table, also if that primary key is referenced in any foreign keys in any table.

possible duplicate of [How do you list the primary key of a SQL Server table?](http://stackoverflow.com/questions/95967/how-do-you-list-the-primary-key-of-a-sql-server-table) — gpgekko, Mar 21 '14 at 13:35
From a SQL Management Studio session you can type `sp_help ` and it'll list a primary key if any along with columns, foreign keys, indices, etc. — Darth Continent, Mar 21 '14 at 13:35

score 1 · Answer 1 · answered Mar 21 '14 at 13:58

Try this:

SELECT  u.COLUMN_NAME as PK, cc.TABLE_NAME, cc.COLUMN_NAME, cc.CONSTRAINT_NAME 
FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE u on c.CONSTRAINT_NAME = u.CONSTRAINT_NAME AND c.CONSTRAINT_TYPE = 'PRIMARY KEY'
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS r on u.CONSTRAINT_NAME = r.UNIQUE_CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cc on cc.CONSTRAINT_NAME = r.CONSTRAINT_NAME
where c.TABLE_NAME = 'your table'

Will give you all tables and columns that are referencing the primary key of the table

score 0 · Answer 2 · answered Mar 21 '14 at 13:37

0

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='tableName'

This will give you your table structure.

answered Mar 21 '14 at 13:37

BRBT

1,467
8
28
48

score 0 · Answer 3 · answered Mar 21 '14 at 13:38

To get the priary keys from a table you can use:

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE = 'PRIMARY KEY' and TABLE_NAME = 'yourTable'

and to get it's references you can use

select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'yourTable'

To get if Primary key Columns is referred as foreign key or not

3 Answers3