How to drop column with constraint?

2018-06-27 07:10:45

How to drop a column which is having Default constraint in SQL Server 2008?

My query is

alter table tbloffers
drop column checkin

I am getting below error

ALTER TABLE DROP COLUMN checkin failed because one or more objects access this column.

Can anyone correct my query to drop a column with constraint?

First you should drop the problematic DEFAULT constraint , after that you can drop the column

alter table tbloffers drop constraint [ConstraintName]
go

alter table tbloffers drop column checkin

But the error may appear from other reasons - for example the user defined function or view with SCHEMABINDING option set for them.

UPD: Completely automated dropping of constraints script:

DECLARE @sql NVARCHAR(MAX)
WHILE 1=1
BEGIN
    SELECT TOP 1 @sql = N'alter table tbloffers drop constraint ['+dc.NAME+N']'
    from sys.default_constraints dc
    JOIN sys.columns c
        ON c.default_object_id = dc.object_id
    WHERE 
        dc.parent_object_id = OBJECT_ID('tbloffers')
    AND c.name = N'checkin'
    IF @@ROWCOUNT = 0 BREAK
    EXEC (@sql)
END

这里有另一种方法可以用一个未知名称删除默认约束，而不必先运行一个单独的查询来获取约束名称：

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 = N'__ColumnName__'
                        AND object_id = OBJECT_ID(N'__TableName__'))
IF @ConstraintName IS NOT NULL
EXEC('ALTER TABLE __TableName__ DROP CONSTRAINT ' + @ConstraintName)

Find the default constraint with this query here:

SELECT
    df.name 'Constraint Name' ,
    t.name 'Table Name',
    c.NAME 'Column Name'
FROM sys.default_constraints df
INNER JOIN sys.tables t ON df.parent_object_id = t.object_id
INNER JOIN sys.columns c ON df.parent_object_id = c.object_id AND df.parent_column_id = c.column_id

This gives you the name of the default constraint, as well as the table and column name.

When you have that information you need to first drop the default constraint:

ALTER TABLE dbo.YourTable
DROP CONSTRAINT name-of-the-default-constraint-here

and then you can drop the column

ALTER TABLE dbo.YourTable DROP COLUMN YourColumn

链接地址: http://www.djcxy.com/p/76320.html

上一篇: 包含数据长度的NAME函数

下一篇: 如何删除约束列？