List All Constraints

/*
sqryConstraints

Created by Larry Ansley 6/10/03.

*/
-- Unique Constraints
Select
Case o1.xtype
When 'C' Then 'Check'
When 'D' Then 'Default'
When 'F' Then 'Foreign Key'
When 'PK' Then 'Primary Key'
When 'UQ' Then 'Unique'
Else 'Other' End as 'Constraint Type',
o.name as 'Table Name',
o1.name as 'Constraint/Index Name',
c1.name as 'Column Name',
k.keyno as 'KeyNo'
From sysobjects o
Join sysobjects o1
on o1.Parent_obj = o.id
Join sysconstraints c
on c.constid = o1.id
Join sysindexes i
on i.id = o.id
and i.name = o1.name
Join sysindexkeys k
on k.id = i.id
and k.indid = i.indid
Join syscolumns c1
on c1.id = k.id
and c1.colid = k.colid
Where o1.xtype = 'UQ'
Order By o.name, o1.name, k.KeyNo


-- Check, Default Constraints
Select
Case o1.xtype
When 'C' Then 'Check'
When 'D' Then 'Default'
When 'F' Then 'Foreign Key'
When 'PK' Then 'Primary Key'
When 'UQ' Then 'Unique'
Else 'Other' End as 'Constraint Type',
o.name as 'Table Name',
o1.name as 'Constraint Name',
c1.name as 'Column Name',
c.text as 'Default/Check Value'
From sysobjects o
Join sysobjects o1
on o1.Parent_obj = o.id
Join syscolumns c1
on c1.id = o1.parent_obj
and c1.colid = o1.info
Join syscomments c
on o1.id = c.id
Where o1.xtype In ('C' , 'D')
Order By o1.xtype, o.name, c1.name


-- Foreign Key Constraints
Select
Case o1.xtype
When 'C' Then 'Check'
When 'D' Then 'Default'
When 'F' Then 'Foreign Key'
When 'PK' Then 'Primary Key'
When 'UQ' Then 'Unique'
Else 'Other' End as 'Constraint Type',
o1.name as 'Constraint Name',
o.name as 'FK Table Name',
c1.name as 'FK Column Name',
c2.name as 'PK Column Name',
o2.name as 'PK Table Table',
fk.keyno as 'KeyNo'
From sysobjects o
Join sysobjects o1
on o1.Parent_obj = o.id
Join sysforeignkeys fk
on fk.constid = o1.id
Join sysobjects o2
on o2.id = fk.rkeyid
Left Join syscolumns c1
 on c1.id = fk.fkeyid
 and c1.colid = fk.fkey
Left Join syscolumns c2
 on c2.id = fk.rkeyid
 and c2.colid = fk.rkey
Where o1.xtype = 'F'
Order By o.name, o2.name, fk.keyno


-- Primary Key Constraints
Select
Case o1.xtype
When 'C' Then 'Check'
When 'D' Then 'Default'
When 'F' Then 'Foreign Key'
When 'PK' Then 'Primary Key'
When 'UQ' Then 'Unique'
Else 'Other' End as 'Constraint Type',
o1.name as 'Constraint Name',
o.name as 'PK Table Name',
c1.name as 'PK Column Name',
c2.name as 'FK Column Name',
o2.name as 'FK Table',
fk.keyno as 'KeyNo'
From sysobjects o
Join sysobjects o1
on o1.Parent_obj = o.id
Join sysforeignkeys fk
on fk.rkeyid = o.id
Join sysobjects o2
on o2.id = fk.fkeyid
Left Join syscolumns c1
 on c1.id = fk.rkeyid
 and c1.colid = fk.rkey
Left Join syscolumns c2
 on c2.id = fk.rkeyid
 and c2.colid = fk.rkey
Where o1.xtype = 'PK'
Order By o.name, o2.name, fk.keyno