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.

Newbie - sql server 2000 transaction log size limit

Has anybody encountered a physical size limit for a sql server 2000 transaction log running on win2k?
Transaction log reached ~6Gb before rolling back the delete stating transaction log was full. There was 42Gb free on the server and the log was set to unlimited growth.Originally posted by axis
Has anybody encountered a physical size limit for a sql server 2000 transaction log running on win2k?

Transaction log reached ~6Gb before rolling back the delete stating transaction log was full. There was 42Gb free on the server and the log was set to unlimited growth.

When the size is set to unlimited I think the limit is as big the win2k can handle?
I had once a log file of 15 Gb big|||usually helps if you make sure the path to the transaction log actualy is where you think it is. Created an extra log on the drive that did have 42gb and all is well.

thanks|||usually helps if you make sure the path to the transaction log actualy is where you think it is. Created an extra log on the drive that did have 42gb and all is well.

thanks

:D Thats true !!! sp_helpdb dbname always helps ...