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