开发者

SQL Query to get the table names which use a particular column as a foreign key

What sql query would you write to get the table names which have a particular foreign key.

Eg. Person_Table(person_id,name,address,...)
    Purchases_Table(purchaseid, purchased_by_id(fkey)....)
    AccountDetails_Table(AccountId,PersonID,....)
    Roles_Table(PersonId,RoleID)
开发者_如何学Go

Now i want to find the table names where Person id is used as a foreign key. The above schema would return me

Purchases_Table,AccountDetails_Table, Roles_Table

Database: SQL Server


select OBJECT_NAME(fkc.parent_object_id) as TableName
    from sys.foreign_key_columns fkc
        inner join sys.columns c
            on fkc.referenced_object_id = c.object_id
                and fkc.referenced_column_id = c.column_id
    where fkc.referenced_object_id = OBJECT_ID('dbo.Person_Table') 
        and c.name = 'person_id'


For postgresql you can do something like this:

SELECT pt.tgargs, pt.tgnargs, pt.tgdeferrable, pt.tginitdeferred,
  pg_proc.proname, pg_proc_1.proname FROM pg_class pc,
  pg_proc pg_proc, pg_proc pg_proc_1, pg_trigger pg_trigger,
  pg_trigger pg_trigger_1, pg_proc pp, pg_trigger pt
WHERE  pt.tgrelid = pc.oid AND pp.oid = pt.tgfoid
  AND pg_trigger.tgconstrrelid = pc.oid
  AND pg_proc.oid = pg_trigger.tgfoid
  AND pg_trigger_1.tgfoid = pg_proc_1.oid
  AND pg_trigger_1.tgconstrrelid = pc.oid
  AND ((pc.relname= '<< DB_NAME >>')
  AND (pp.proname LIKE '%%ins')
  AND (pg_proc.proname LIKE '%%upd')
  AND (pg_proc_1.proname LIKE '%%del')
  AND (pg_trigger.tgrelid=pt.tgconstrrelid)
  AND (pg_trigger_1.tgrelid = pt.tgconstrrelid));

See this post (from 2000!) on the Postgresql mailing list.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜