Listing database constarints

List all table of database with there constraints. Its a useful query to list all database constraints. Yet a simple query but useful You can further filter them by table name , constraint type etc.

The query simply query sys.objects and fetches tables and there constraints. SCHEMA_NAME a scaler function returns schema's name by id. and OBJECT_NAME returns Object(table, view etc) name by its ID. type_desc defines the scripts constraint type (Foreign key, Primary key, default etc).

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects

Using some filters (lists all foreign key contraints in DB)

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE 'FOREIGN_KEY_CONSTRAINT'

Following query list down all FK contstraints of tabel specified

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE 'FOREIGN_KEY_CONSTRAINT' AND OBJECT_NAME(parent_object_id)=''

Further Improved version

select
o1.name as Referencing_Object_name
, c1.name as referencing_column_Name
, o2.name as Referenced_Object_name
, c2.name as Referenced_Column_Name
, s.name as Constraint_name
from sysforeignkeys fk
inner join sysobjects o1 on fk.fkeyid = o1.id
inner join sysobjects o2 on fk.rkeyid = o2.id
inner join syscolumns c1 on c1.id = o1.id and c1.colid = fk.fkey
inner join syscolumns c2 on c2.id = o2.id and c2.colid = fk.rkey
inner join sysobjects s on fk.constid = s.id
and o2.name='

Happy Coding :)