pick only DEFAULT constraints from sysconstraints

Is this just for SQL Server 2000?  I ask because you have posted to SQL Server 2008, yet you are using the deprecated SQL Server 2000 System table.

In any case if you still need to use that old table, in order to get the DEFAULT constraints all you have to do is add the following WHERE clause:
WHERE status & 5 = 5

thanks- that helped.

i chose sysconstraints because it was neutral to all environments...

for 2008, i came up with
select name,OBJECT_NAME(parent_object_id) ,*  from sys.all_objects where type = 'd'

but i am not able to pull the column name.. can you advise?

but i am not able to pull the column name.. can you advise?
For which version SQL Server 2000 or 2008? The answer should be different in each case.

Assuming you are still using SQL Server 2000, then this is how you would have to do it:

SELECT  o.name, c.name, oc.name
FROM    sysobjects o
	INNER JOIN syscolumns c ON o.id = c.id
	INNER JOIN sysconstraints ct ON o.id = ct.id AND c.colid = ct.colid
	INNER JOIN sysobjects oc ON ct.constid = oc.id
WHERE	o.xtype = 'U'
	AND ct.status & 5  = 5

Select allOpen in new window

acperkins- thanks for that. sorry i should have kept the intended zone more clear..

can you kindly give me the syntax for 2008 to get column name? appreciate that.