CODEWITHSUNDEEP
sqlsql-server
Shivam Shukla
Shivam Shukla

Reputation: 23

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

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.

Upvotes: 1

Views: 226

Answers (3)

Boklucius
Boklucius

Reputation: 1926

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

Upvotes: 1

Fedor Hajdu
Fedor Hajdu

Reputation: 4697

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'

Upvotes: 0

BRBT
BRBT

Reputation: 1487

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

This will give you your table structure.

Upvotes: 0

Related Questions