July 2, 2004 at 7:24 am
Hi,
I'm trying to compile a report on foreign key (FK) data which could be considered complete (see script at bottom).
I would, however, like to add the actual foreign key constraint name in sysobjects to the forray and end up with this result: FK table and column name, the referenced table (RK) and column name AND the relevant FK constraint name with the UPDATE and DELETE rule (relationship properties).
I have tried to join sysobjects.parent_obj on the FK id but this is not correct and requires a join on the ordinal position. I've scoured INFORMATION_SCHEMA and sys% tables but haven't been able to put the lot together any better. What I'm really missing is a system table that has the FK id and the FK table id, RK table id and both the ordinal positions or column id's.
/*
Does: Step 1: Inserts table and column information into a temporary table
Step 2: Returns: Column 1: FK - table with foreign key
Column 2: FC - the column name within the foreign key table
Column 3: fkey - the column ordinal position within the foreign key table
Column 4: RK - related key table
Column 5: RC - the column name within the related key table
Column 6: rkey - the column ordinal position within the related key table
Done: 20040701
By: MvZ
*/
-- Step 1
SELECT TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME
INTO __tmpTBLCOL
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN (SELECT NAME FROM sysobjects WHERE xtype = 'u')
-- Step 2
SELECT FO.name AS FK, FC.Column_Name AS FC, FK.fkey, RO.name AS RK, RC.Column_Name AS RC, FK.rkey --FK.fkeyid, FK.rkeyid,
FROM dbo.sysforeignkeys FK
INNER JOIN dbo.__tmpTBLCOL FC ON FK.fkey = FC.Ordinal_Position
INNER JOIN dbo.__tmpTBLCOL RC ON FK.rkey = RC.Ordinal_Position
LEFT OUTER JOIN dbo.sysobjects RO ON RC.Table_Name = RO.name AND FK.rkeyid = RO.id
LEFT OUTER JOIN dbo.sysobjects FO ON FC.Table_Name = FO.name AND FK.fkeyid = FO.id
WHERE (NOT (FO.name IS NULL)) AND (NOT (RO.name IS NULL))
ORDER BY FO.name
-- DROP TABLE __tmpTBLCOL
I know creating the temporary table is quite unprofessional but nested joins affect the performance quite drastically.
TIA
Max
Max
July 2, 2004 at 8:35 am
SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
joined into the recordset above would, for example, be the perfect solution, but I still don't know how.
How does sql server display the foreign key name and the related table and column in the relationships from table design? There must therefore be a way.
Max
Max
July 2, 2004 at 8:43 am
Not sure, if I fully understand you, but I think you're missing link is INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE. So something like this
SELECT
C.TABLE_NAME
, C.ORDINAL_POSITION
, C.COLUMN_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
INNER JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
ON
RC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.COLUMNS C
ON
CCU.TABLE_NAME = C.TABLE_NAME
WHERE
C.TABLE_NAME IN (SELECT NAME FROM sysobjects WHERE xtype = 'u')
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
July 2, 2004 at 9:42 am
Thanks, that aught to do it! I'll post the result here when I'm finished.
Max
Max
July 2, 2004 at 10:10 am
As promised:
SELECT FO.name AS FK, FC.Column_Name AS FC, FK.fkey, RO.name AS RK, RC.Column_Name AS RC, FK.rkey, CN.CONSTRAINT_NAME, RL.UPDATE_RULE, RL.DELETE_RULE
FROM dbo.sysforeignkeys FK
INNER JOIN dbo.__tmpTBLCOL FC ON FK.fkey = FC.Ordinal_Position
INNER JOIN dbo.__tmpTBLCOL RC ON FK.rkey = RC.Ordinal_Position
LEFT OUTER JOIN dbo.sysobjects RO ON RC.Table_Name = RO.name AND FK.rkeyid = RO.id
LEFT OUTER JOIN dbo.sysobjects FO ON FC.Table_Name = FO.name AND FK.fkeyid = FO.id
INNER JOIN (SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE) CN ON FO.Name = CN.TABLE_NAME AND FC.Column_Name = CN.COLUMN_NAME
INNER JOIN (SELECT CONSTRAINT_NAME, UNIQUE_CONSTRAINT_NAME, UPDATE_RULE, DELETE_RULE FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS) RL ON CN.CONSTRAINT_NAME = RL.CONSTRAINT_NAME
WHERE (NOT (FO.name IS NULL)) AND (NOT (RO.name IS NULL)) --AND FO.name = 'SIPP_Fees'
ORDER BY FO.name
You'll have to use the "temporary" that was created in the first post as well.
Max
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply