Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2007
    Posts
    8

    Question Unanswered: 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:
    Code:
    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
    Code:
    sp_depends AllNetCategories
    and I cannot see any object name like "DF_AllNetCategories_ActionTime" dependent on it.

    Can anyone please help?

  2. #2
    Join Date
    May 2007
    Posts
    49
    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')
    Mihir Mehendale
    DB Team @ Clarion
    Clarion Technologies
    (SEI CMMI Level 3)
    Pune, India
    www.clariontechnologies.co.in
    www.vEmployee.com

  3. #3
    Join Date
    Aug 2007
    Posts
    8
    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?

  4. #4
    Join Date
    May 2007
    Posts
    49
    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
    Mihir Mehendale
    DB Team @ Clarion
    Clarion Technologies
    (SEI CMMI Level 3)
    Pune, India
    www.clariontechnologies.co.in
    www.vEmployee.com

  5. #5
    Join Date
    Aug 2007
    Posts
    8
    It works~ Thank you Mihir

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    sp_helpconstraint may be easier for you to use and is available in SQL 2000 and SQL 2005.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •