constraintdetail

SELECT OBJECT_NAME(object_id) AS ConstraintName,
type_desc AS ConstraintType,
SCHEMA_NAME(schema_id) + '.' + OBJECT_NAME(parent_object_id) AS TableName
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT' and SCHEMA_NAME(schema_id) = 'metadata'
order by OBJECT_NAME(parent_object_id),ConstraintType,ConstraintName

SELECT OBJECT_NAME(object_id) AS ConstraintName,
lower(replace(type_desc,'_',' ')) + ' in table: ' + SCHEMA_NAME(schema_id) + '.' + OBJECT_NAME(parent_object_id) AS TableName
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT' and SCHEMA_NAME(schema_id) = 'metadata'
order by SCHEMA_NAME(schema_id) + '.' + OBJECT_NAME(parent_object_id),OBJECT_NAME(parent_object_id),ConstraintName





SELECT OBJECT_NAME(object_id) AS ConstraintName,'',
lower(replace(type_desc,'_',' ')) + ' in table: ' + SCHEMA_NAME(schema_id) + '.' + OBJECT_NAME(parent_object_id) + isnull(' referenced with table '+SCHEMA_NAME(schema_id)+'.'+refertablename,'') AS TableName

FROM sys.objects a
left join (SELECT f.name,OBJECT_NAME(fc.referenced_object_id) refertablename,
   OBJECT_NAME(f.parent_object_id) TableName,
   COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
FROM
   sys.foreign_keys AS f
INNER JOIN
   sys.foreign_key_columns AS fc
      ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN
   sys.tables t
      ON t.OBJECT_ID = fc.referenced_object_id) b
	  on a.name = b.name
WHERE type_desc LIKE '%CONSTRAINT' --and SCHEMA_NAME(schema_id) = 'metadata'
order by SCHEMA_NAME(schema_id) + '.' + OBJECT_NAME(parent_object_id),OBJECT_NAME(parent_object_id),ConstraintName