Script to list all PK & FK keys?

Question

Post reply

Script to list all PK & FK keys?

shwetha

Ten Centuries

Points: 1248
More actions
August 6, 2007 at 4:56 pm

#70089

Thanks in advance!
Does anyone have a Script to list all the Primary keys & Foreign Keys with table names in the database?
There are many tables in the db's & I need to find out which tables have what keys?
Regards.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

the script below will get you started; the core table you are looking for is sysforeignkeys

typical results:

CONSTRAINTNAME	REFTABLE	REFCOLUMN	FKTABLE	FKCOLUMN	FKSQL
FK__GEOMOON__GEOPLAN__01C9240F	GEOPLANET	GEOPLANETID	GEOMOON	GEOPLANETID	ALTER TABLE GEOMOON ADD CONSTRAINT FK__GEOMOON__GEOPLAN__01C9240F FOREIGN KEY (GEOPLANETID) REFERENCES GEOPLANET(GEOPLANETID)
FK__GEOCONTIN__GEOPL__0B528E49	GEOPLANET	GEOPLANETID	GEOCONTINENT	GEOPLANETID	ALTER TABLE GEOCONTINENT ADD CONSTRAINT FK__GEOCONTIN__GEOPL__0B528E49 FOREIGN KEY (GEOPLANETID) REFERENCES GEOPLANET(GEOPLANETID)
FK__GEOCONTIN__GEOCO__0E2EFAF4	GEOCONTINENT	GEOCONTINENTID	GEOCONTINENTAREAS	GEOCONTINENTID	ALTER TABLE GEOCONTINENTAREAS ADD CONSTRAINT FK__GEOCONTIN__GEOCO__0E2EFAF4 FOREIGN KEY (GEOCONTINENTID) REFERENCES GEOCONTINENT(GEOCONTINENTID)
FK__GEOCOUNTR__GEOCO__3DDE0E16	GEOCONTINENT	GEOCONTINENTID	GEOCOUNTRY	GEOCONTINENTID	ALTER TABLE GEOCOUNTRY ADD CONSTRAINT FK__GEOCOUNTR__GEOCO__3DDE0E16 FOREIGN KEY (GEOCONTINENTID) REFERENCES GEOCONTINENT(GEOCONTINENTID)

script:

SELECT

OBJECT_NAME(constid) AS CONSTRAINTNAME,

OBJECT_NAME(rkeyid) AS REFTABLE,

COL_NAME(rkeyid,rkey) AS REFCOLUMN,

OBJECT_NAME(fkeyid) AS FKTABLE,

COL_NAME(fkeyid,fkey) AS FKCOLUMN,

' ALTER TABLE ' + OBJECT_NAME(fkeyid)

+ ' ADD CONSTRAINT ' + OBJECT_NAME(constid)

+ ' FOREIGN KEY (' + COL_NAME(fkeyid,fkey)

+ ') REFERENCES ' + OBJECT_NAME(rkeyid)

+'(' + COL_NAME(rkeyid,rkey) + ')' AS FKSQL

from sysforeignkeys

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

-- Get Primary keys
select
 *
from
 INFORMATION_SCHEMA.TABLE_CONSTRAINTS a
where
 CONSTRAINT_TYPE = 'PRIMARY KEY'

-- Get Foreign Keys
select *
from
 INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS a

-- PK/FK matches
select distinct
 PK_TABLE = 
 b.TABLE_SCHEMA+'.'+b.TABLE_NAME,
 FK_TABLE = 
 c.TABLE_SCHEMA+'.'+c.TABLE_NAME
from
 INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS a
 join
 INFORMATION_SCHEMA.TABLE_CONSTRAINTS b
 on
 a.CONSTRAINT_SCHEMA = b.CONSTRAINT_SCHEMA and
 a.UNIQUE_CONSTRAINT_NAME = b.CONSTRAINT_NAME
 join
 INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
 on
 a.CONSTRAINT_SCHEMA = c.CONSTRAINT_SCHEMA and
 a.CONSTRAINT_NAME = c.CONSTRAINT_NAME
order by
 1,2

CAGreensfelder SSC Eights! Points: 919 More actions · Answer 3

Thank you. I learn so much from here.

Michael - Very nice solution! I've never worked with the INFORMATION_SCHEMA tables before, only sysobjects like Jeff Moden's solution to the same problem at http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=146&messageid=388318 Must admit, INFORMATION_SCHEMA gives a more readable output.

Jeff - Also very nice. I use sysobjects all the time to get a list of FK or PK. I've never tried to get a report hooking them together.

Cathy Greensfelder

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

I prefer to use INFORMATION_SCHEMA for this type of work because it is an industry standard schema, and less likely to change if Microsoft changes the structure of the system tables in later versions.

You may find the script on the link below useful for analyzing the correct order for loading or deleting from tables with FK references.

Find Table Reference Levels

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72957

Jeff Moden SSC Guru Points: 1004425 More actions · Answer 5

I don't have access to 2k5, yet, Michael... did they keep the information schema views named the same way?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

All the ones I have looked at seem to be the same, but I can't say I was ever looking for differences. I just didn't notice anything breaking.

Jeff Moden SSC Guru Points: 1004425 More actions · Answer 7

Heh... nothing breaking is good

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

shwetha Ten Centuries Points: 1248 More actions · Answer 8

Hey Jeff, Thanks for the help.

Do you have SQL Script to find out when data was last updated/modified in the table?

Thanks.

Jeff Moden SSC Guru Points: 1004425 More actions · Answer 9

No... because it can't be done. If you don't have triggers to force the update of ModifiedOn and ModifiedBy columns in every table, there's nothing you can do.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)