foreign key name and column ordinal position

  • 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

  • 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

  • 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]

  • Thanks, that aught to do it! I'll post the result here when I'm finished.

    Max

    Max

  • 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