Jack Corbett - Thursday, December 28, 2017 4:03 PM

Here's how I get Foreign Key info:

SELECT
  FK.name,
  SCHEMA_NAME(FK.schema_id) AS schemaName,
  OBJECT_NAME(FK.parent_object_id) AS parentTableName,
  parentCols.name AS parentColumnName,
  OBJECT_NAME(FK.referenced_object_id) AS referencedTableName,
  referencedCols.name AS referencedColumnName
FROM
  sys.foreign_keys AS FK
  JOIN sys.foreign_key_columns AS FKCols
   ON FK.parent_object_id = FKCols.parent_object_id AND
    FK.object_id = FKCols.constraint_object_id AND
    FK.referenced_object_id = FKCols.referenced_object_id
  JOIN sys.columns AS parentCols
   ON FKCols.parent_object_id = parentCols.object_id AND
    FKCols.parent_column_id = parentCols.column_id
  JOIN sys.columns AS referencedCols
   ON FKCols.referenced_object_id = referencedCols.object_id AND
    FKCols.referenced_column_id = referencedCols.column_id;

Here's how I'd get other constraints:


SELECT
  DC.name AS ConstraintName,
  DC.type_desc AS ConstraintType,
  DC.definition AS ConstraintDefinition,
  OBJECT_SCHEMA_NAME(DC.schema_id) AS SchemaName,
  OBJECT_NAME(DC.parent_object_id) AS TableName,
  C.name AS ColumnName
FROM
  sys.default_constraints AS DC
  JOIN sys.columns AS C
   ON DC.parent_object_id = C.object_id AND
    DC.parent_column_id = C.column_id
UNION ALL
SELECT
  CC.name,
  CC.type_desc,
  CC.definition,
  CC.schema_id,
  OBJECT_SCHEMA_NAME(CC.schema_id) AS SchemaName,
  OBJECT_NAME(CC.parent_object_id),
  C.name AS ColumnName
FROM
  sys.check_constraints AS CC
  JOIN sys.columns AS C
   ON CC.parent_object_id = C.object_id AND
    CC.parent_column_id = C.column_id
UNION ALL
SELECT
  KC.name,
  KC.type_desc,
  NULL AS Defintion,
  OBJECT_SCHEMA_NAME(KC.schema_id) AS SchemaName,
  OBJECT_NAME(KC.parent_object_id),
  C.name
FROM
  sys.key_constraints AS KC
  JOIN sys.indexes AS I
   ON KC.unique_index_id = I.index_id AND
    KC.parent_object_id = I.object_id
  JOIN sys.index_columns AS IC
   ON I.index_id = IC.index_id AND
    I.object_id = IC.object_id
  JOIN sys.columns AS C
   ON IC.column_id = C.column_id AND
    IC.object_id = C.object_id