pick only DEFAULT constraints from sysconstraints

how can you filter only the DF constraints from the below:?
 SELECT object_name(constid),object_name(id),COL_NAME(id,colid) FROM sysconstraints
Who is Participating?
Anthony PerkinsConnect With a Mentor Commented:
I believe we covered that in your other thread. In any case, here it goes again:
SELECT  t.name,
FROM    sys.tables t
        INNER JOIN sys.columns c ON t.object_id = c.object_id
        INNER JOIN sys.default_constraints dc ON t.object_id = dc.parent_object_id
                                                 AND c.column_id = dc.parent_column_id

Open in new window

Anthony PerkinsCommented:
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.
Anthony PerkinsCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

25112Author Commented:
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?
Anthony PerkinsCommented:
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.
Anthony PerkinsCommented:
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

Open in new window

25112Author Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.