--Build the table that gives us PK column name to FK column name resolution
        insert into #constraints
        select     o1.name,c1.name,o2.name,c2.name,key_seq
        from     #fkeys f, sysobjects o1, sysobjects o2, syscolumns c1, syscolumns c2
        where    o1.id = f.pktable_id
        AND o2.id = f.fktable_id
        AND c1.id = f.pktable_id
        AND c2.id = f.fktable_id
        AND c1.colid = f.pkcolid
        AND c2.colid = f.fkcolid
        AND f.fk_id = @constid
We need sysobjects and syscolumns in this query twice since we need both the parent table and column as well as the child table and column.

After this table is built all that is left is to return it to the user.   After that, some cleanup is performed and the process is started over with the next table that references the table in the outer loop.  Once the inner loop has been completed, some more clean up is performed and the entire process is restarted for the next table in the list.  Finally the final cleanup is done after finsihing all tables in the database.

For a full treatment of both table folding and table pivoting, refer to the books by Joe Celko and David Rozenshstein et al.  These were the basis for putting this script together and provide some very powerful techniques.  You could possibly use a case construct to do this, but that is not portable to another DBMS.   This query technique should be portable to any of the 5 major DBMSes.  As noted in the short review of the Rozenshstein et al book, I have still to make it past the first 23 pages and here is another application in a long list of them for the material presented in these few short pages.

One other thing that should be noted to those worrying about performance and who adhere to the "bible" of what to eliminate from your query plans.   (I have not run a query plan on this script.)  I can almost guarantee that you will see worktables and worktables created for reformatting (the worst offender of query performance according to all performance and tuning sources) within the query plans of either a table pivot or a table fold that use this technique.  But, you will not find a query that performs better than these techniques.  Why?  Because in order to eliminate those red flags from your query plan you have to do this in multiple steps (the 32 insert queries in this case).  This requires 32 passes thorugh the data which is very costly.  The technique outlined above does one pass through the data which is a savings of 31 passes.  While it does give up some of that savings by creating a cross product, it still takes less time to process.

Find Foreign Keys 1 2 3 4 5