Default Constraint

[agoeddeke]

Hello,

How can I check to see if a default constraint exists on a column and what the name of the constraint is? I want to eventually drop the column, but need to check and drop the constraint first.

Thanks,
Andy

 

[john76]

Is this a default you created explicitly or within the table definition?


If explicit, you should be able to query sysobjects for the name you gave it. If in the table def, I am sure you can still do the same thing, but figuring out the name SQL Server gave the constraint might be a little involved...

HTH,
John

 

[agoeddeke]

Is this a default you created explicitly or within the table definition?

The name was created within the table definition.

...but figuring out the name SQL Server gave the constraint might be a little involved...

This is exactly what I'm trying to figure out.

Thanks,
Andy

 

[agoeddeke]

Just to follow up... this is what I ended up with for anyone interested:

DECLARE @cTable VARCHAR(100), @cColumn VARCHAR(100)
SET @cTable = 'myTable'
SET @cColumn = 'myColumn'
SELECT sysobjects.name from sysobjects
	JOIN syscolumns ON 
		(sysobjects.id = syscolumns.cdefault 
		AND sysobjects.parent_obj = syscolumns.id)
	WHERE sysobjects.parent_obj = object_id(@cTable) 
		AND sysobjects.xtype IN 
		 ('C ','PK','UQ','F ', 'D ')
		AND syscolumns.name = @cColumn

Andy