Geek Answers Handbook

Finding foreign keys in ms sql server using information_schema

I am trying to find all tables with foreign keys pointing to a specific table. So I wrote the following:

select t1.TABLE_NAME as pointsfrom, t2.TABLE_NAME as pointsto
        from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS r
        join INFORMATION_SCHEMA.TABLE_CONSTRAINTS t1 on t1.CONSTRAINT_NAME=r.CONSTRAINT_NAME
        join INFORMATION_SCHEMA.TABLE_CONSTRAINTS t2 on t2.CONSTRAINT_NAME=r.UNIQUE_CONSTRAINT_NAME
        where t2.table_name = @mytable

This works in 90% of cases, and more precisely, for 22 of the 24 foreign keys in the database I'm working on. But for 2 FK, the name unique_constraint_name does not match any name in table_constraints.

The names are also a little funny. One of them is the "designation of the environment", with space. The field that FK points to is called the "environment_designator", with an underscore. Another has a unique configuration_name "file name unique" that does not match anything that I see in the definition of the "from" or "to" table.

Is there a place somewhere else, should I look for a match on unique_constraint_name?

+5
source share
1 answer

Try the following:

SELECT OBJECT_NAME(parent_object_id), OBJECT_NAME(referenced_object_id)
    FROM sys.foreign_keys
    WHERE referenced_object_id = OBJECT_ID(@mytable)
+7
source

More articles:


All Articles