SQL Server 2005 drop column with constraints

Question

I have a column with a "DEFAULT" constraint. I'd like to create a script that drops that column.

The problem is that it returns this error:

Msg 5074, Level 16, State 1, Line 1  
The object 'DF__PeriodSce__IsClo__4BCC3ABA' is dependent on column 'IsClosed'. 
Msg 4922, Level 16, State 9, Line 1 
ALTER TABLE DROP COLUMN IsClosed failed because one or more objects access this column.

I couldn't find an easy way to drop a column and all its associated constraints (only found big scripts that look into the system table... there MUST (!!) be a "nice" way to do it.)

And as the DEFAULT constraint's name has been randomly generated, I can't drop it by name.

Update :
The constraint type is "DEFAULT".

I saw the solutions that you all proposed but I find them all really "dirty"... Don't you think? I don't know if it's with Oracle or MySQL but it's possible to do something like:

DROP COLUMN xxx CASCADE CONSTRAINTS

And it drops all related constraints... Or at least it automatically drops the constraints mapped to that column (at least CHECK constraints!)

Is there nothing like that in MSSQL?

Jeremy Stein · Answer 1 · 2014-01-07T19:47:13.200

64

Here is a script that will delete the column along with its default constraint. Replace MYTABLENAME and MYCOLUMNNAME appropriately.

declare @constraint_name sysname, @sql nvarchar(max)

select @constraint_name = name 
from sys.default_constraints 
where parent_object_id = object_id('MYTABLENAME')
AND type = 'D'
AND parent_column_id = (
    select column_id 
    from sys.columns 
    where object_id = object_id('MYTABLENAME')
    and name = 'MYCOLUMNNAME'
    )

set @sql = N'alter table MYTABLENAME drop constraint ' + @constraint_name
exec sp_executesql @sql

alter table MYTABLENAME drop column MYCOLUMNNAME

go

edited Jan 07 '14 at 19:47

answered Jul 23 '09 at 13:51

Jeremy Stein

19,171
16
68
83

nice, this looks like what I am after. – CRice Oct 28 '10 at 01:58
I needed to do 'alter table MYTABLENAME drop column MYCOLUMNNAME' – Bill Comer May 10 '11 at 14:20
1

Is there any practical reason why a sub-query was used, instead of a join? – Josh Russo Dec 07 '11 at 18:22
1

No. It just reflects the way my brain approached the problem. – Jeremy Stein Dec 08 '11 at 14:05
The type column is inherited from sys.objects and the sys.default_constraints view already filters on type = 'D' as such the "AND type = 'D'" is unnecessary. – Martin Brown Aug 26 '14 at 10:38

edosoft · Accepted Answer · 2010-04-22T13:45:08.807

22

This query finds default constraints for a given table. It aint pretty, I agree:

select 
    col.name, 
    col.column_id, 
    col.default_object_id, 
    OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst, 
    dobj.name as def_name
from sys.columns col 
    left outer join sys.objects dobj 
        on dobj.object_id = col.default_object_id and dobj.type = 'D' 
where col.object_id = object_id(N'dbo.test') 
and dobj.name is not null

[EDIT] Updated per Julien N's comment

edited Apr 22 '10 at 13:45

answered Dec 12 '08 at 10:36

edosoft

17,121
25
77
111

1

Your answer is right but it doesn't do the same thing as mine. You get the constraints from one specific table, mine gets all constraints. Two things : syntax error (remove the comma before from) and you should add "and dobj.name is not null" in the WHERE clause to remove the non-relevant results. – Julien N Feb 03 '09 at 14:20

score 16 · Answer 3 · answered Jul 24 '10 at 19:57

Perhaps it could help a little more:

declare @tablename nvarchar(200)
declare @colname nvarchar(200)
declare @default sysname, @sql nvarchar(max)

set @tablename = 'your table'
set @colname = 'column to drop'

select @default = name 
from sys.default_constraints 
where parent_object_id = object_id(@tablename)
AND type = 'D'
AND parent_column_id = (
    select column_id 
    from sys.columns 
    where object_id = object_id(@tablename)
    and name = @colname 
    )

set @sql = N'alter table ' + @tablename + ' drop constraint ' + @default
exec sp_executesql @sql

set @sql = N'alter table ' + @tablename + ' drop column ' + @colname
exec sp_executesql @sql

Only need to set the @tablename & @colname variables to drop the column.

really good answer, together with [@Jeremy Stein's answer](http://stackoverflow.com/a/1171895/662581) help me create my own to solve my problem. — Michel Ayres, Jul 25 '12 at 20:08

score 6 · Answer 4 · answered Nov 25 '08 at 13:53

> select CONSTRAINT_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
> WHERE TABLE_NAME = '<tablename>' AND COLUMN_NAME = 'IsClosed'

It's not the right solution as it is explained here : http://msdn.microsoft.com/en-us/library/aa175912.aspx that :

Unfortunately, the name of the column default constraint isn't kept in the ANSI COLUMNS view, so you must go back to the system tables to find the name

The only way I found to get the name of the DEFAULT constraint is this request :

select  
    t_obj.name              as TABLE_NAME
    ,c_obj.name             as CONSTRAINT_NAME
    ,col.name               as COLUMN_NAME

from    sysobjects  c_obj
join    sysobjects  t_obj on c_obj.parent_obj = t_obj.id  
join    sysconstraints con on c_obj.id  = con.constid
join    syscolumns  col on t_obj.id = col.id
            and con.colid = col.colid
where
    c_obj.xtype = 'D'

Am I the only one to find it crazy to be unable to delete easily a constraint that only concerns the columns I'm trying to drop ?
I need to execute a request with 3 joins just to get the name...

Like string splitting / concatenation, this is yet another situation where everyone has to implement their own kludgy workaround, when the functionality should have come with SQL Server itself. — sayap, May 23 '12 at 10:55

score 6 · Answer 5 · answered Nov 07 '10 at 10:58

I also think it's a shortcoming in SQL server to not have a cascading drop available. I worked my way around it by querying the system tables in the same way as other people described here:

INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE only lists foreign keys, primary keys and unique constraints.
The only way to look for default constraints is to look for them in sys.default_constraints.
what hasn't been mentioned here yet, is that indexes also make dropping a column fail, so you also need to drop all indexes that use your column before you can proceed with dropping a column.

The resulting script is not pretty, but I put it in a stored procedure to be able to reuse it:

CREATE PROCEDURE DropColumnCascading @tablename nvarchar(500), @columnname nvarchar(500)
AS

SELECT CONSTRAINT_NAME, 'C' AS type
INTO #dependencies
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = @tablename AND COLUMN_NAME = @columnname

INSERT INTO #dependencies
select d.name, 'C'
from sys.default_constraints d
join sys.columns c ON c.column_id = d.parent_column_id AND c.object_id = d.parent_object_id
join sys.objects o ON o.object_id = d.parent_object_id
WHERE o.name = @tablename AND c.name = @columnname

INSERT INTO #dependencies
SELECT i.name, 'I'
FROM sys.indexes i
JOIN sys.index_columns ic ON ic.index_id = i.index_id and ic.object_id=i.object_id
JOIN sys.columns c ON c.column_id = ic.column_id and c.object_id=i.object_id
JOIN sys.objects o ON o.object_id = i.object_id
where o.name = @tableName AND i.type=2 AND c.name = @columnname AND is_unique_constraint = 0

DECLARE @dep_name nvarchar(500)
DECLARE @type nchar(1)

DECLARE dep_cursor CURSOR
FOR SELECT * FROM #dependencies

OPEN dep_cursor

FETCH NEXT FROM dep_cursor 
INTO @dep_name, @type;

DECLARE @sql nvarchar(max)

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 
        CASE @type
            WHEN 'C' THEN 'ALTER TABLE [' + @tablename + '] DROP CONSTRAINT [' + @dep_name + ']'
            WHEN 'I' THEN 'DROP INDEX [' + @dep_name + '] ON dbo.[' + @tablename + ']'
        END
    print @sql
    EXEC sp_executesql @sql
    FETCH NEXT FROM dep_cursor 
    INTO @dep_name, @type;
END

DEALLOCATE dep_cursor

DROP TABLE #dependencies

SET @sql = 'ALTER TABLE [' + @tablename + '] DROP COLUMN [' + @columnname + ']'

print @sql
EXEC sp_executesql @sql

score 5 · Answer 6 · answered Aug 31 '11 at 01:07

The answer from pvolders was just what I needed but it missed statistics which were causing and error. This is the same code, minus creating a stored procedure, plus enumerating statistics and dropping them. This is the best I could come up with so if there is a better way to determine what statistics need to be dropped please add.

DECLARE @tablename nvarchar(500), 
        @columnname nvarchar(500)

SELECT  @tablename = 'tblProject',
        @columnname = 'CountyKey'


SELECT CONSTRAINT_NAME, 'C' AS type
INTO #dependencies
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = @tablename AND COLUMN_NAME = @columnname

INSERT INTO #dependencies
select d.name, 'C'
from sys.default_constraints d
join sys.columns c ON c.column_id = d.parent_column_id AND c.object_id = d.parent_object_id
join sys.objects o ON o.object_id = d.parent_object_id
WHERE o.name = @tablename AND c.name = @columnname

INSERT INTO #dependencies
SELECT i.name, 'I'
FROM sys.indexes i
JOIN sys.index_columns ic ON ic.index_id = i.index_id and ic.object_id=i.object_id
JOIN sys.columns c ON c.column_id = ic.column_id and c.object_id=i.object_id
JOIN sys.objects o ON o.object_id = i.object_id
where o.name = @tableName AND i.type=2 AND c.name = @columnname AND is_unique_constraint = 0

INSERT INTO #dependencies
SELECT s.NAME, 'S'
FROM sys.stats AS s
INNER JOIN sys.stats_columns AS sc 
    ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id
INNER JOIN sys.columns AS c 
    ON sc.object_id = c.object_id AND c.column_id = sc.column_id
WHERE s.object_id = OBJECT_ID(@tableName)
AND c.NAME = @columnname
AND s.NAME LIKE '_dta_stat%'

DECLARE @dep_name nvarchar(500)
DECLARE @type nchar(1)

DECLARE dep_cursor CURSOR
FOR SELECT * FROM #dependencies

OPEN dep_cursor

FETCH NEXT FROM dep_cursor 
INTO @dep_name, @type;

DECLARE @sql nvarchar(max)

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 
        CASE @type
            WHEN 'C' THEN 'ALTER TABLE [' + @tablename + '] DROP CONSTRAINT [' + @dep_name + ']'
            WHEN 'I' THEN 'DROP INDEX [' + @dep_name + '] ON dbo.[' + @tablename + ']'
            WHEN 'S' THEN 'DROP STATISTICS [' + @tablename + '].[' + @dep_name + ']'
        END
    print @sql
    EXEC sp_executesql @sql
    FETCH NEXT FROM dep_cursor 
    INTO @dep_name, @type;
END

DEALLOCATE dep_cursor

DROP TABLE #dependencies

SET @sql = 'ALTER TABLE [' + @tablename + '] DROP COLUMN [' + @columnname + ']'

print @sql
EXEC sp_executesql @sql

score 4 · Answer 7 · answered Jul 26 '12 at 12:50

Just to build on Jeremy Stein's answer, I created a stored procedure for this, and set it up so it can be used to delete a column that has or does not have default constraints. It's not real efficient since it's querying sys.columns twice, but it works.

CREATE PROCEDURE [dbo].[RemoveColumnWithDefaultConstraints] 
    -- Add the parameters for the stored procedure here
    @tableName nvarchar(max), 
    @columnName nvarchar(max)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @ConstraintName nvarchar(200)
    SELECT @ConstraintName = Name 
    FROM SYS.DEFAULT_CONSTRAINTS 
    WHERE PARENT_OBJECT_ID = OBJECT_ID(@tableName) 
        AND PARENT_COLUMN_ID = (SELECT column_id FROM sys.columns WHERE NAME = (@columnName) 
        AND object_id = OBJECT_ID(@tableName))
    IF @ConstraintName IS NOT NULL
        EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @ConstraintName)

    IF EXISTS(SELECT * FROM sys.columns WHERE Name = @columnName  
        AND Object_ID = Object_ID(@tableName))
        EXEC('ALTER TABLE ' + @tableName + ' DROP COLUMN ' + @columnName)       
END
GO

score 4 · Answer 8 · answered Nov 24 '08 at 18:49

4

You can get the constraint names by querying the information_schema system views.

select CONSTRAINT_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = '<tablename>' AND COLUMN_NAME = 'IsClosed'

answered Nov 24 '08 at 18:49

Steve Sheldon

110
3

2

This does not return default constraints, as mentioned below – edosoft Dec 12 '08 at 10:37

score 2 · Answer 9 · answered Jul 20 '11 at 11:55

Looking up the name of the contraint or using MSSQL design view is not always an option. I currently want to make a script for deleting a column with a contraint on it. Using the name is not an option since the name is generated and I want to use the script in different environments Dev/Sys/Prod/etc. Using the name is then not possible because the contraint names will differ per environment. I will probably have to look into the systems tabel but I agree there should be an easier option available.

score 1 · Answer 10 · answered Nov 24 '08 at 19:07

1

I believe explicitly dropping the constraints prior to dropping the column is a "cleaner" solution. This way, you don't drop constraints you may not be aware of. If the drop still fails, you know there are additional constraints remaining. I like being in control of exactly what is happening to my database.

Plus, scripting the drops explicitly guarantees the script and hopefully the results to be repeatable in exactly the way you intend.

answered Nov 24 '08 at 19:07

DCNYAM

11,966
8
53
70

1

"you don't drop constraints you may not be aware of" - I agree. But not for constraints that only applies on the column dropped... I guess I'll have to request the database to know the name of the constraint in order to be able to drop it... – Julien N Nov 25 '08 at 10:56

score 0 · Answer 11 · answered Nov 24 '08 at 18:41

What do you mean randomly generated? You can look up the constraints on the specific column in management studio or via the sys.tables view and find what the name(s) are.

Then, you can change your script to drop the constraints prior to dropping the column. What type of constraint is this? If it is a foreign key constraint, make sure that doing this won't hurt the data integrity within you database.

score 0 · Answer 12 · answered Jul 19 '11 at 14:59

0

I just ran into this. You can delete the column with constraints using MSSQL design view. Right click on the column you want to be dropped (with or without constraints) and you are able to delete this without any problems. Ha.. I looked stupid already.

answered Jul 19 '11 at 14:59

Bitmask

918
2
11
22

1

Yes you can delete it through Enterprise Manager. The goal was to do it by script. I had no direct access to the sql server instance. – Julien N Jul 21 '11 at 12:46

score -1 · Answer 13 · answered Dec 12 '08 at 09:29

-1

Just Generate Scripts for the table. There you can find the name of all constraints.

answered Dec 12 '08 at 09:29

SQL Server 2005 drop column with constraints

13 Answers13

Linked