How to get table constraint information in SQL Server

Question

I need to output the constraint information for a given table. I've found lots of closely relevant information here at SO, but I'm not finding a couple of specific pieces of information.

I need the output to look like this in the query results:

CONSTRAINT_NAME CONSTRAINT_TYPE COLUMN_NAME REFERENCED_TABLE REFERENCED_COLUMN

Currently, I have this query:

SELECT
obj_Constraint.NAME AS CONSTRAINT_NAME,
obj_Constraint.type_desc AS CONSTRAINT_TYPE,
'' AS COLUMN_NAME,
'' AS REFERENCED_TABLE,
columns.NAME AS REFERENCED_COLUMN
FROM sys.objects AS obj_table 
LEFT JOIN sys.objects AS obj_Constraint ON obj_table.object_id = obj_Constraint.parent_object_id 
LEFT JOIN sys.sysconstraints AS constraints ON constraints.constid = obj_Constraint.object_id 
LEFT JOIN sys.columns AS columns ON columns.object_id = obj_table.object_id AND columns.column_id = constraints.colid 
WHERE obj_table.name = 'some_table'
ORDER BY obj_Constraint.type_desc

Where do I get the referenced (foreign key) table and column information from?

(I'd need a query compatible for SQL Server versions 2008 & later)

EDIT

Based on initial responses, I may not have been clear on the desired output.

Say I have a table "people" with columns "poepleID", "name", "city", "stateID". The Primary Key is on the poepleID column. I also have a table "states" with "stateID" and "state". The Primary Key on that is on the stateID column. You can guess that I have a Foreign Key constraint on people.stateID. I'll call that FK__people__states__stateID

So I need a query that will output this:

CONSTRAINT_NAME                 CONSTRAINT_TYPE            COLUMN_NAME     REFERENCED_TABLE     REFERENCED_COLUMN
PK__people                      PRIMARY_KEY_CONSTRAINT     peopleID              
FK__people__states__stateID     FOREIGN_KEY_CONSTRAINT     stateID         states               stateID

I need to list all key constraints (primary, foreign - any) on the table in question ("people" in this case).

EzLo · Answer 1 · 2019-01-08T16:26:59.547

Use the schema view INFORMATION_SCHEMA.TABLE_CONSTRAINTS and INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.

This query shows all foreign keys, for example:

SELECT
    ConstraintName = C.CONSTRAINT_NAME,
    PrimaryKeyTable = QUOTENAME(PK.CONSTRAINT_SCHEMA) + '.' + QUOTENAME(PK.TABLE_NAME),
    PrimaryKeyColumn = CCU.COLUMN_NAME,
    ForeignKeyTable = QUOTENAME(FK.CONSTRAINT_SCHEMA) + '.' + QUOTENAME(FK.TABLE_NAME),
    ForeignKeyColumn = CU.COLUMN_NAME,
    UpdateRule = C.UPDATE_RULE,
    DeleteRule = C.DELETE_RULE
FROM
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C 
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON 
        C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME AND
        C.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG AND
        C.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON 
        C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME AND
        C.UNIQUE_CONSTRAINT_CATALOG = PK.CONSTRAINT_CATALOG AND
        C.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON 
        C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME AND
        C.CONSTRAINT_CATALOG = CU.CONSTRAINT_CATALOG AND
        C.CONSTRAINT_SCHEMA = CU.CONSTRAINT_SCHEMA
    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU ON 
        PK.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME AND
        PK.CONSTRAINT_CATALOG = CCU.CONSTRAINT_CATALOG AND
        PK.CONSTRAINT_SCHEMA = CCU.CONSTRAINT_SCHEMA
WHERE
    FK.CONSTRAINT_TYPE = 'FOREIGN KEY'
ORDER BY
    PK.TABLE_NAME, 
    FK.TABLE_NAME

I believe you are capable of working your way around this to get the results you want in a single query.

Example:

CREATE TABLE PKTable (
    PKColumn INT PRIMARY KEY,
    CheckValue INT,
    CHECK (CheckValue > 0))

CREATE TABLE FKTable (
    FKColumn INT, 
    FOREIGN KEY (FKColumn) REFERENCES PKTable (PKColumn))


DECLARE @TableName VARCHAR(100) = 'PKTable'

-- Primary keys, checks
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS T WHERE T.TABLE_NAME = @TableName

-- Foreign keys
SELECT
    ConstraintName = C.CONSTRAINT_NAME,
    PrimaryKeyTable = QUOTENAME(PK.CONSTRAINT_SCHEMA) + '.' + QUOTENAME(PK.TABLE_NAME),
    PrimaryKeyColumn = CCU.COLUMN_NAME,
    ForeignKeyTable = QUOTENAME(FK.CONSTRAINT_SCHEMA) + '.' + QUOTENAME(FK.TABLE_NAME),
    ForeignKeyColumn = CU.COLUMN_NAME,
    UpdateRule = C.UPDATE_RULE,
    DeleteRule = C.DELETE_RULE
FROM
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C 
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON 
        C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME AND
        C.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG AND
        C.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON 
        C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME AND
        C.UNIQUE_CONSTRAINT_CATALOG = PK.CONSTRAINT_CATALOG AND
        C.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON 
        C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME AND
        C.CONSTRAINT_CATALOG = CU.CONSTRAINT_CATALOG AND
        C.CONSTRAINT_SCHEMA = CU.CONSTRAINT_SCHEMA
    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU ON 
        PK.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME AND
        PK.CONSTRAINT_CATALOG = CCU.CONSTRAINT_CATALOG AND
        PK.CONSTRAINT_SCHEMA = CCU.CONSTRAINT_SCHEMA
WHERE
    FK.CONSTRAINT_TYPE = 'FOREIGN KEY' AND
    PK.TABLE_NAME = @TableName
ORDER BY
    PK.TABLE_NAME, 
    FK.TABLE_NAME

Results:

CONSTRAINT_CATALOG  CONSTRAINT_SCHEMA   CONSTRAINT_NAME                 TABLE_CATALOG   TABLE_SCHEMA    TABLE_NAME  CONSTRAINT_TYPE     IS_DEFERRABLE   INITIALLY_DEFERRED
RS1                 dbo                 PK__PKTable__1EEFCD814EF90F36   RS1             dbo             PKTable     PRIMARY KEY         NO              NO
RS1                 dbo                 CK__PKTable__CheckVa__6DBA0403  RS1             dbo             PKTable     CHECK               NO              NO


ConstraintName                  PrimaryKeyTable     PrimaryKeyColumn    ForeignKeyTable     ForeignKeyColumn    UpdateRule  DeleteRule
FK__FKTable__FKColum__6FA24C75  [dbo].[PKTable]     PKColumn            [dbo].[FKTable]     FKColumn            NO ACTION   NO ACTION

score 0 · Answer 2 · answered Jan 08 '19 at 15:50

0

I think you could use sysreferences and check this post for the code. There is also a tool called Advanced SQL Server Dependencies that may be useful.

answered Jan 08 '19 at 15:50

Marc0

181
7

How to get table constraint information in SQL Server

2 Answers2