SELECT
pt.COLUMN_NAME AS PrimaryField,
c.CONSTRAINT_NAME AS KeyName,
fk.TABLE_NAME AS ForeignTable,
cu.COLUMN_NAME AS ForeignField
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk ON
c.CONSTRAINT_NAME = fk.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ON
c.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu ON
c.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT tc.TABLE_NAME, kcu.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
) pt ON pt.TABLE_NAME = pk.TABLE_NAME
WHERE pk.TABLE_NAME = 'MyTableName'
ORDER BY foreignTable ASC;
Tuesday, December 8, 2009
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment