January 9, 2008 at 2:25 am
Hi all,
wherever we create a Relationship in more than two table, The how to another developer show what type of relationship create in table?
Can anyone explain me..
Rgds
Sanjay
January 9, 2008 at 9:14 am
Can you explain the question in detail? Its too confusing.
SQL DBA.
January 9, 2008 at 10:52 am
You may try the following script.
SELECT OBJECT_NAME(rkeyid) ReferencedTable FROM sysreferences WHERE fkeyid = OBJECT_ID('yourTableName')
January 9, 2008 at 11:41 am
Not sure, but check if this query helps?
Create Table #t (
[constraint] nvarchar(255),
PKTable nvarchar(255),
PKColumn nvarchar(255),
FKTable nvarchar(255),
FKColumn nvarchar(255)
)
Declare @tblID int
Declare c Cursor
Read_Only
FOR
SELECT id FROM sysobjects WHERE xtype = 'U'
OPEN c
Fetch c into @tblID
WHILE (@@Fetch_Status = 0)
BEGIN
INSERT #t
SELECT
obj3.[name] AS [Constraint],
obj1.[name] AS PKTable,
col1.[name] AS PKColumn,
obj2.[name] AS FKTable,
col2.[name] AS FKColumn
FROM sysforeignkeys AS fk
JOIN sysconstraints AS con ON fk.constid = con.constid
JOIN sysobjects AS obj1 ON fk.rkeyid = obj1.id
JOIN sysobjects AS obj2 ON fk.fkeyid = obj2.id
JOIN sysobjects AS obj3 ON fk.constid = obj3.id
JOIN syscolumns AS col1 ON fk.rkeyid = col1.id AND fk.rkey = col1.colid
JOIN syscolumns AS col2 ON fk.fkeyid = col2.id AND fk.fkey = col2.colid
WHERE rkeyid = @tblID
or fkeyid =@tblID
Fetch c into @tblID
END
CLOSE c
DEALLOCATE c
SELECT * FROM #t
DROP TABLE #t
SQL DBA.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply