Newbie encounts DF_TableName_ColumnName errors

Hi Guys,

I am new to the database administration game. I encountered a dependency issue when I was trying to change the datatype of a column from smalldatetime to datetime.

This is the code I use:

ALTER table AllNetCategories ALTER COLUMN actiontime datetime
Go

When I run the above code, the database comes back this error message
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF_AllNetCategories_ActionTime' is dependent on column 'actiontime'.

I also checked the dependency on the target table using

sp_depends AllNetCategories


and I cannot see any object name like "DF_AllNetCategories_ActionTime" dependent on it.

Can anyone please help?It looks like 'DF_AllNetCategories_ActionTime' is a default constraint,
if it is so, sp_depends procedure is not going to list it down.
Query sys.default_constraints view instead -

Select * from sys.default_constraints
where [Parent_object_id] = object_id('AllNetCategories')|||Thanks mihirclarion.
I am using SQL 2000 and I can not find sys.default_constraints table.
The only table I can find those 'DF_%' is at sysobjects.
How can I find out what those DF_% constraints for?|||you can use Information_schema views to know about constraints exists -

Select * from information_schema.REFERENTIAL_CONSTRAINTS where constraint_name like '<Constraint_name>'
Select * from information_schema.CHECK_CONSTRAINTS where constraint_name like '<Constraint_name>'
Select * from information_schema.TABLE_CONSTRAINTS where constraint_name like '<Constraint_name>'

or you can also use a simple query like -

Select * from sysobjects where name like '<Constraint_name>'
and check value of 'xtype' column -
D - Default
FN - Function
P - Procedure
PK - Primary key
U - User tables
S - System table ... etc.

In general, prefix 'DF_' stands for default colstraints
Query smiliar to following can give you details you need -

Select inf_cols.* from sysobjects sysobj1
INNER JOIN sysconstraints syscon1
INNER JOIN syscolumns syscols1 ON syscols1.colid = syscon1.colid and syscols1.id = syscon1.id
ON syscon1.Constid = sysobj1.ID
INNER JOIN information_schema.columns inf_cols ON inf_cols.Column_name = syscols1.name
and object_id(inf_cols.table_name) = syscols1.id
where sysobj1.name like '<Constraint_name>' and inf_cols.column_default is not null|||It works~ Thank you Mihir|||sp_helpconstraint may be easier for you to use and is available in SQL 2000 and SQL 2005.