Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2004
    Posts
    84

    Unanswered: dropping columns with default constraints

    Hi, I want to drop a column from a table with default constraint. It is giving me error..
    ------------------
    Server: Msg 5074, Level 16, State 1, Line 1
    The object 'DF__ACTIVITY___ROLLU__108B795B' is dependent on column 'ROLLUP_BGT_COST_FIXED'.
    Server: Msg 4922, Level 16, State 1, Line 1
    ALTER TABLE DROP COLUMN ROLLUP_BGT_COST_FIXED failed because one or more objects access this column.
    -------------------------

    Here is the drop statement
    -----------
    DROP STATISTICS ACTIVITY_BASELINE.ROLLUP_BGT_COST_FIXED
    ALTER TABLE ACTIVITY_BASELINE DROP COLUMN ROLLUP_BGT_COST_FIXED;
    ------------------------

    How can I first drop the constraint?
    Kishore

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    alter table ACTIVITY_BASELINE
    drop constraint DF__ACTIVITY___ROLLU__108B795B
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Mar 2004
    Posts
    84
    Hi, I have done this before but, this doesnt work always as the defaults are created on databases with different names. How can I drop the default constraint for a column in a generic way?
    Kishore

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The easy solution is to name the constraint when you create it, so that you can use the name when you delete it.

    The hard way is to dynamically create the drop from the system tables with the name from the database.

    -PatP

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You can use something like this:
    Code:
    
     
    if object_id('dbo.sp_dropdefconstraint') is not null
    drop procedure dbo.sp_dropdefconstraint
    go
    create procedure dbo.sp_dropdefconstraint (
    @table_name nvarchar(128),
    @column_id	 int				)
    as
    declare @cmd nvarchar(4000), @id int, @constid int
    select @id = object_id(@table_name)
    select @constid = min(constid)
    	 from dbo.sysconstraints
    	 where id = @id
    	 and colid = @column_id
    	 and objectproperty(constid, 'IsDefaultCnst') = 1
    if @constid is null begin
    	 raiserror ('Failed to locate DEFAULT constraint for specified table!', 15, 1)
    	 return (1)
    end
    select @cmd = 'alter table ' + object_name(@id) +
    	 ' drop constraint ' + object_name(@constid)
    exec (@cmd)
    return (0)
    go
    
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Mar 2004
    Posts
    84
    Quote Originally Posted by rdjabarov
    You can use something like this:
    Code:
    
     
    if object_id('dbo.sp_dropdefconstraint') is not null
    drop procedure dbo.sp_dropdefconstraint
    go
    create procedure dbo.sp_dropdefconstraint (
    @table_name nvarchar(128),
    @column_id	 int				)
    as
    declare @cmd nvarchar(4000), @id int, @constid int
    select @id = object_id(@table_name)
    select @constid = min(constid)
    	 from dbo.sysconstraints
    	 where id = @id
    	 and colid = @column_id
    	 and objectproperty(constid, 'IsDefaultCnst') = 1
    if @constid is null begin
    	 raiserror ('Failed to locate DEFAULT constraint for specified table!', 15, 1)
    	 return (1)
    end
    select @cmd = 'alter table ' + object_name(@id) +
    	 ' drop constraint ' + object_name(@constid)
    exec (@cmd)
    return (0)
    go
    
    Hi Thanks for the snippet...What is the usage like..
    I want to
    ALTER TABLE ACTIVITY_BASELINE DROP COLUMN ROLLUP_BGT_COST_FIXED
    How do I pass these values into the procedure?
    Kishore

  7. #7
    Join Date
    Mar 2004
    Posts
    84
    Hi Nevermind, I have written a cursor to drop the selected constraints...:-)
    -------------------
    DECLARE @cid INT
    DECLARE @temp VARCHAR (100)
    DECLARE @stmt VARCHAR (300)
    DECLARE DEF_CURSOR cursor for
    select colid from dbo.syscolumns
    where id = object_id('ACTIVITY_BASELINE') and name in
    ( 'ROLLUP_BGT_COST_FIXED', 'ROLLUP_ACT_COST_FIXED', 'ROLLUP_EST_COST_FIXED',
    'ROLLUP_BGT_COST_CAPITAL', 'ROLLUP_ACT_COST_CAPITAL', 'ROLLUP_EST_COST_CAPITAL',
    'ROLLUP_BGT_COST_FLEX', 'ROLLUP_ACT_COST_FLEX', 'ROLLUP_EST_COST_FLEX')
    OPEN DEF_CURSOR
    WHILE (0 = 0)
    BEGIN --(
    FETCH next from DEF_CURSOR into @cid
    IF (@@FETCH_STATUS = -1)
    BREAK
    select @temp = object_name(constid) from dbo.sysconstraints where id = object_id('ACTIVITY_BASELINE') and status & 5 = 5 and colid = @cid
    SELECT @stmt = 'ALTER TABLE ACTIVITY_BASELINE DROP CONSTRAINT ' + @temp
    exec (@stmt)
    END --)
    close DEF_CURSOR
    DEALLOCATE DEF_CURSOR
    GO
    -----------------------
    Kishore

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I personally like WHILE (1=1)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Mar 2004
    Posts
    84
    Quote Originally Posted by rdjabarov
    I personally like WHILE (1=1)
    Any special reson for that?
    Kishore

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    That was just a joke!

    All you had to do was to run this statement (which you already have):

    select "exec dbo.sp_dropdefconstraint N'" + object_name(id) + "', " + cast(colid as varchar(10)) from dbo.syscolumns
    where id = object_id('ACTIVITY_BASELINE') and name in
    ( 'ROLLUP_BGT_COST_FIXED', 'ROLLUP_ACT_COST_FIXED', 'ROLLUP_EST_COST_FIXED',
    'ROLLUP_BGT_COST_CAPITAL', 'ROLLUP_ACT_COST_CAPITAL', 'ROLLUP_EST_COST_CAPITAL',
    'ROLLUP_BGT_COST_FLEX', 'ROLLUP_ACT_COST_FLEX', 'ROLLUP_EST_COST_FLEX')

    The resulting script can then be copied, pasted, and run to accomplish the same result, but without a cursor.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Mar 2004
    Posts
    84
    :-)...Will do that...
    Kishore

Posting Permissions

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