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)
开发者_如何学GoNow 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.
精彩评论