CODEWITHSUNDEEP
mysqlsqloracle-databasepostgresql
user769394
user769394

Reputation:

Get all tables where a primary key is linked to a foreign key

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?

Upvotes: 1

Views: 4481

Answers (3)

Tathagat Verma
Tathagat Verma

Reputation: 548

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.

Upvotes: 1

user769394
user769394

Reputation:

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'

Upvotes: 0

rahularyansharma
rahularyansharma

Reputation: 10755

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

Upvotes: 0

Related Questions