Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Foreign and Primary Key extraction

Author  Topic 

jkhome2
Starting Member

5 Posts

Posted - 2005-10-19 : 12:25:50
I have an application in which i need to get the foreign key fields from a table and then get all the foreign keys primary key field from the linking table. Could some one tell me how i do this using INFORMATION_SCHEMA. I have tried and can get the foreign keys but not sure how to get the associated primary keys.

Kristen
Test

22859 Posts

Posted - 2005-10-19 : 13:17:16
Will this do the trick?

-- Display the Primary Key columns for a give table
SELECT KCU.COLUMN_NAME
WHERE INFORMATION_SCHEMA.table_constraints TC
JOIN INFORMATION_SCHEMA.key_column_usage KCU
ON KCU.CONSTRAINT_CATALOG = TC.CONSTRAINT_CATALOG
AND KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND TC.TABLE_NAME = 'MyTableName'
ORDER BY TC.TABLE_NAME, KCU.ordinal_position

Kristen
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-19 : 13:17:34
Why not just create the ERD instead rather than using T-SQL to do this? If you don't have ERwin or Visio, then you can use the Diagram option in Enterprise Manager. From that, you can visually see the keys.

Tara
Go to Top of Page
   

- Advertisement -