1

Can I retrieve all the table names and the associated column names where a primary key is linked to a foreign key in another table?

E.g.: I have a table EMPLOYEE and its primary key is linked to by a foreign key in many other tables, say DEPT, ACCOUNT and so on.

Is there a way that I can can get the table and column names of those tables where I have used employee's Primary key as a Foreign Key?

  • lokk at this : http://stackoverflow.com/questions/806989/mysql-how-to-i-find-all-tables-that-have-foreign-keys-that-reference-particular – Louis Oct 05 '11 at 08:47
  • 6
    Did you try to read your question? Why do you type in such a way? It's incredibly DIFFICULT to read. You can query `information_schema.referential_constraints` and obtain the info you need. Look up the docs to see how to use it. – N.B. Oct 05 '11 at 08:51

3 Answers3

1

You could use the "View Dependencies" feature for the primary-table to get that info, in the SSMS. Also there some tools available that help you do that, one such in "SQLSpec". It's a pretty good tool for database documentation.

http://www.sql-server-performance.com/2007/sqlspec/

Hope this helps.

Tathagat Verma
  • 549
  • 7
  • 12
0

by chance today i got the same query in my mind and using google i found the following sql 

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'

you can update the LIKE '%CONSTRAINT' as per your need.

i use this in sqlserver2005

rahularyansharma
  • 11,156
  • 18
  • 79
  • 135
0

Thanks A lot Everyone... i Did it in Postgresql Where in i fired the following Query:

SELECT constraint_name 
FROM information_schema.referential_constraints 
WHERE unique_constraint_name='table_name_pkey'
Kevin Burton
  • 11,676
  • 2
  • 24
  • 37