Friday 15 December 2017

List out Table's Primary Key Referencing Foreign Key Tables

If you have lots of tables in your high-end Database that time you are confusing about Primary and Foreign Key relationship, Because There is Many To Many Cross Relationship Possible. To Find out Table's Primary Key references with other tables as Foreign Key in database. You can easily List out Referencing tables of PK_Table.
For Example,

You use AdventureWorks Database. Now you want to find Foreign Key referencing of BusinessEntityID from person.Person table from AdventureWorks. Execute Following Command.

Use AdventureWorks
GO

SELECT
    c.CONSTRAINT_NAME,
    cu.TABLE_NAME AS ReferencingTable, cu.COLUMN_NAME AS ReferencingColumn,
    ku.TABLE_NAME AS ReferencedTable, ku.COLUMN_NAME AS ReferencedColumn
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu
ON cu.CONSTRAINT_NAME = c.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku
ON ku.CONSTRAINT_NAME = c.UNIQUE_CONSTRAINT_NAME
WHERE ku.TABLE_NAME = 'Person(PK_Table)'

You will get List of Foreign Key References like,


No comments:

Post a Comment

Youtube blocked hack play youtube video from embedded url

Youtube introduced new feature for playing video in own website or third party application using embed feature. Youtube blocked in some of ...