Andreas_Sprosec_7439

How to check exisiting Constraint via SQL script?

Discussion created by Andreas_Sprosec_7439 on Sep 2, 2016
For a SQL Server database you can use the script below:

e.g table OFS:

select object_name(a.parent_object_id)     as ParentTable
      ,object_name(a.referenced_object_id) as ChildTable
      ,c.name                              as ChildColumn
      ,b.name                              as ParentColumn
      ,object_name(a.constraint_object_id) as ConstraintName
FROM SYS.FOREIGN_KEY_COLUMNS A
JOIN SYS.COLUMNS B ON A.PARENT_COLUMN_ID  = B.COLUMN_ID  AND A.PARENT_OBJECT_ID     = B.OBJECT_ID
JOIN SYS.COLUMNS C ON A.referenced_COLUMN_ID=C.COLUMN_ID AND A.REFERENCED_OBJECT_ID = C.OBJECT_ID
 where object_name(a.parent_object_id) = 'OFS'
;

xnphd32gmeiu.png

If you know the name of the constraint but not the table you can use this script:

e.g.: Name of the constraint is FK_OFS_OH_F

select object_name(a.parent_object_id)     as ParentTable
      ,object_name(a.referenced_object_id) as ChildTable
      ,b.name                              as ParentColumn
      ,c.name                              as ChildColumn
      ,object_name(a.constraint_object_id) as ConstraintName
FROM SYS.FOREIGN_KEY_COLUMNS A
JOIN SYS.COLUMNS B ON A.PARENT_COLUMN_ID  = B.COLUMN_ID  AND A.PARENT_OBJECT_ID     = B.OBJECT_ID
JOIN SYS.COLUMNS C ON A.referenced_COLUMN_ID=C.COLUMN_ID AND A.REFERENCED_OBJECT_ID = C.OBJECT_ID
 where object_name(a.constraint_object_id) = 'FK_OFS_OH_F'
;

f318ud096dlr.png

Outcomes