June 3, 2004 at 6:11 am
Is there anyway of getting the list of FK with the table name
My Blog:
June 3, 2004 at 7:18 am
Dinesh,
See if what is in this article will work for you.http://qa.sqlservercentral.com/columnists/jyao/makinggooduseofsysforeignkeystablepart1displaytabl.asp
June 3, 2004 at 7:18 am
SELECT FK.[Name], TBL.[Name]
FROM sysobjects FK INNER JOIN sysobjects TBL
ON FK.parent_obj = TBL.id
WHERE FK.xtype ='F'
Did like above . is there any other straigh forward way of doing it
My Blog:
June 4, 2004 at 2:35 am
I USED THIS FROM MSDN http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro03/html/sp03l12.asp
Hope this helps
SELECT
KCU1.CONSTRAINT_NAME AS 'FK_CONSTRAINT_NAME'
, KCU1.TABLE_NAME AS 'FK_TABLE_NAME'
, KCU1.COLUMN_NAME AS 'FK_COLUMN_NAME'
, KCU1.ORDINAL_POSITION AS 'FK_ORDINAL_POSITION'
, KCU2.CONSTRAINT_NAME AS 'UQ_CONSTRAINT_NAME'
, KCU2.TABLE_NAME AS 'UQ_TABLE_NAME'
, KCU2.COLUMN_NAME AS 'UQ_COLUMN_NAME'
, KCU2.ORDINAL_POSITION AS 'UQ_ORDINAL_POSITION'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1
ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2
ON KCU2.CONSTRAINT_CATALOG =
RC.UNIQUE_CONSTRAINT_CATALOG
AND KCU2.CONSTRAINT_SCHEMA =
RC.UNIQUE_CONSTRAINT_SCHEMA
AND KCU2.CONSTRAINT_NAME =
RC.UNIQUE_CONSTRAINT_NAME
AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply