How do I obtain information about a nullable foreign key from Information_Schema views?

Question

I'm using the INFORMATION_SCHEMA views in Sql Server 2005 & 2008 to obtain metadata for a database:

SELECT
    PK.TABLE_NAME as 'PK_TABLE_NAME',
    FK.TABLE_NAME as 'FK_TABLE_NAME',
    C.CONSTRAINT_NAME as 'CONSTRAINT_NAME'
FROM
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
    JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
    JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
WHERE
    FK.TABLE_NAME = 'Table_Name'

However, if I have a nullable foreign key on the table it is not included in my results because there is no matching entry for the "UNIQUE_CONSTRAINT_NAME" column.

I'm struggling to work out how to obtain information about nullable foreign keys (specifically, the name of the referenced table and column) using views in the INFORMATION_SCHEMA schema. Apparently indexes aren't part of the standard, and hence aren't included in the views.

Does anyone know how I might alter my query to obtain information about nullable foreign keys?

Edit

As an aside, it would appear that SQL Server Compact Edition has an INFORMATION_SCHEMA.INDEXES view - why does CE get this useful information?!

Answer 1

I've had a look and it's not the nullable FKs, but where the FK points to a unique index rather than a unique constraint/primary key it look like to me.

Even though both are implemented as indexes, a unique index does not appear in TABLE_CONSTRAINTS

There is no "INFORMATION_SCHEMA.INDEXES".

So, the option would be to use the "sys" views...

Edit: using sys.indexes for the missing bit. And I don't know why SQL CE has the view...

SELECT
    ISNULL(PK.TABLE_NAME, OBJECT_NAME(I.[object_id])) as 'PK_TABLE_NAME',
    FK.TABLE_NAME as 'FK_TABLE_NAME',
    C.CONSTRAINT_NAME as 'CONSTRAINT_NAME'
FROM
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
    JOIN
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
    LEFT JOIN
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
    LEFT JOIN
    sys.indexes I ON C.UNIQUE_CONSTRAINT_NAME = I.[name]

Answer 2

jusy look at the source code for sp_helpconstraint

In management Studio, go into:

  Databases  
    System Databases  
      master  
        Programmability  
          Stored Procedures  
            System Stored Procedures  
              sp_helpconstraint

You can see exactly hos Microsoft does it. There are may other system stored procedures that you can look into that will "show" you how to get table meta data....