Finding Missing Foreign Keys

In another solution I asked to find all occurances of a particular field name in the DB.  I have some missing foreign keys and I want to find all instances of a column named "order_id" in the DB so I can FK them all back to the PK orders.order_id

I was provided with this script which did a great job:

SELECT t.name, c.name
FROM sys.tables t
    JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.name = 'order_id'
ORDER BY t.name, c.name

How would I modify this to add a column to tell me if the result c.name is already an FK back to my original table?
ccleebeltPresidentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
try this:

EXEC sp_fkeys 'orders';

>> look for FKTABLE_NAME & FKCOLUMN_NAME in particular

and maybe try this?
SELECT
        OBJECT_NAME(b.parent_object_id) AS FK_Table_Name
      , c.name                          AS FK_Column_Name
FROM sys.foreign_keys a
JOIN sys.foreign_key_columns b
        ON a.object_id = b.constraint_object_id
JOIN sys.columns c
        ON b.constraint_column_id = c.column_id
        AND b.parent_object_id = c.object_id
JOIN sys.columns d
        ON b.referenced_column_id = d.column_id
        AND b.referenced_object_id = d.object_id
WHERE OBJECT_NAME(b.referenced_object_id) = 'orders'
        AND c.name = 'order_id'
ORDER BY
        c.name

Open in new window

0
ccleebeltPresidentAuthor Commented:
Thanks Paul - follow up.  How could we use the above to identify any of the "order_id" occurances that do not have an FK?
0
PortletPaulfreelancerCommented:
this is untested, but I think using NOT EXISTS would be the simplest way to leverage both queries
SELECT t.name, c.name
FROM sys.tables t
    JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.name = 'order_id'
AND NOT EXISTS (
                SELECT 1
                FROM sys.foreign_keys a
                JOIN sys.foreign_key_columns b
                        ON a.object_id = b.constraint_object_id
                JOIN sys.columns cols
                        ON b.constraint_column_id = cols.column_id
                        AND b.parent_object_id = cols.object_id
                JOIN sys.columns d
                        ON b.referenced_column_id = d.column_id
                        AND b.referenced_object_id = d.object_id
                WHERE OBJECT_NAME(b.referenced_object_id) = 'orders'
                        AND cols.name = c.name
                        AND OBJECT_NAME(b.parent_object_id) = t.name
                )
ORDER BY t.name, c.name

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.