How to drop column with constraint?

Sql ServerSql Server-2008

Sql Server Problem Overview

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?

Sql Server Solutions

Solution 1 - Sql Server

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

Solution 2 - Sql Server

Here's another way to drop a default constraint with an unknown name without having to first run a separate query to get the constraint name:

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)

Solution 3 - Sql Server

You can also drop the column and its constraint(s) in a single statement rather than individually.

CREATE TABLE #T
  (
     Col1 INT CONSTRAINT UQ UNIQUE CONSTRAINT CK CHECK (Col1 > 5),
     Col2 INT
  )

ALTER TABLE #T DROP CONSTRAINT UQ , 
                    CONSTRAINT CK, 
                    COLUMN Col1


DROP TABLE #T

##Some dynamic SQL that will look up the names of dependent check constraints and default constraints and drop them along with the column is below (but not other possible column dependencies such as foreign keys, unique and primary key constraints, computed columns, indexes)

CREATE TABLE [dbo].[TestTable]
(
A INT DEFAULT '1' CHECK (A=1),
B INT,
CHECK (A > B)
)

GO

DECLARE @TwoPartTableNameQuoted nvarchar(500) = '[dbo].[TestTable]',
	    @ColumnNameUnQuoted sysname = 'A',
		@DynSQL NVARCHAR(MAX);

SELECT @DynSQL =
     'ALTER TABLE ' + @TwoPartTableNameQuoted + ' DROP' + 
      ISNULL(' CONSTRAINT ' + QUOTENAME(OBJECT_NAME(c.default_object_id)) + ',','') + 
	  ISNULL(check_constraints,'') + 
      '  COLUMN ' + QUOTENAME(@ColumnNameUnQuoted)
FROM   sys.columns c
       CROSS APPLY (SELECT ' CONSTRAINT ' + QUOTENAME(OBJECT_NAME(referencing_id)) + ','
                    FROM   sys.sql_expression_dependencies
                    WHERE  referenced_id = c.object_id
                           AND referenced_minor_id = c.column_id
                           AND OBJECTPROPERTYEX(referencing_id, 'BaseType') = 'C'
                    FOR XML PATH('')) ck(check_constraints)
WHERE  c.object_id = object_id(@TwoPartTableNameQuoted)
       AND c.name = @ColumnNameUnQuoted;

PRINT @DynSQL;
EXEC (@DynSQL);

Solution 4 - Sql Server

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

Solution 5 - Sql Server

The following worked for me against a SQL Azure backend (using SQL Server Management Studio), so YMMV, but, if it works for you, it's waaaaay simpler than the other solutions.

ALTER TABLE MyTable
	DROP CONSTRAINT FK_MyColumn
	CONSTRAINT DK_MyColumn
    -- etc...
	COLUMN MyColumn
GO

Solution 6 - Sql Server

I got the same:

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

My column had an index which needed to be deleted first. Using sys.indexes did the trick:

DECLARE @sql VARCHAR(max)

SELECT @sql = 'DROP INDEX ' + idx.NAME + ' ON tblName'
FROM sys.indexes idx
INNER JOIN sys.tables tbl ON idx.object_id = tbl.object_id
INNER JOIN sys.index_columns idxCol ON idx.index_id = idxCol.index_id
INNER JOIN sys.columns col ON idxCol.column_id = col.column_id
WHERE idx.type <> 0
	AND tbl.NAME = 'tblName'
	AND col.NAME = 'colName'

EXEC sp_executeSql @sql
GO

ALTER TABLE tblName
DROP COLUMN colName

Solution 7 - Sql Server

It's not always just a default constraint that prevents from droping a column and sometimes indexes can also block you from droping the constraint. So I wrote a procedure that drops any index or constraint on a column and the column it self at the end.

IF OBJECT_ID ('ADM_delete_column', 'P') IS NOT NULL
   DROP procedure ADM_delete_column;
GO

CREATE procedure ADM_delete_column
	@table_name_in 	nvarchar(300)
,	@column_name_in nvarchar(300)
AS 
BEGIN
	/* 	Author: Matthis ([email protected] at 2019.07.20)
		License CC BY (creativecommons.org)
		Desc: 	Administrative procedure that drops columns at MS SQL Server
				- if there is an index or constraint on the column 
					that will be dropped in advice
				=> input parameters are TABLE NAME and COLUMN NAME as STRING
	*/
	SET NOCOUNT ON

	--drop index if exist (search first if there is a index on the column)
	declare @idx_name VARCHAR(100)
	SELECT	top 1 @idx_name = i.name
	from	sys.tables t
	join	sys.columns c
	on		t.object_id = c.object_id
	join	sys.index_columns ic
	on		c.object_id = ic.object_id
	and		c.column_id = ic.column_id
	join	sys.indexes i
	on		i.object_id = ic.object_id
	and		i.index_id = ic.index_id
	where	t.name like @table_name_in
	and		c.name like @column_name_in
	if		@idx_name is not null
	begin 
		print concat('DROP INDEX ', @idx_name, ' ON ', @table_name_in)
		exec ('DROP INDEX ' + @idx_name + ' ON ' + @table_name_in)
	end

	--drop fk constraint if exist (search first if there is a constraint on the column)
	declare @fk_name VARCHAR(100)
	SELECT	top 1 @fk_name = CONSTRAINT_NAME 
	from 	INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
	where	TABLE_NAME like @table_name_in
	and		COLUMN_NAME like @column_name_in
	if		@fk_name is not null
	begin 
		print concat('ALTER TABLE ', @table_name_in, ' DROP CONSTRAINT ', @fk_name)
		exec ('ALTER TABLE ' + @table_name_in + ' DROP CONSTRAINT ' + @fk_name)
	end

	--drop column if exist
	declare @column_name VARCHAR(100)
	SELECT	top 1 @column_name = COLUMN_NAME 
	FROM	INFORMATION_SCHEMA.COLUMNS 
	WHERE	COLUMN_NAME like concat('%',@column_name_in,'%')
	if	@column_name is not null
	begin 
		print concat('ALTER TABLE ', @table_name_in, ' DROP COLUMN ', @column_name)
		exec ('ALTER TABLE ' + @table_name_in + ' DROP COLUMN ' + @column_name)
	end
end;
GO


--to run the procedure use this execute and fill the parameters 
execute ADM_delete_column 
	@table_name_in 	= ''
,	@column_name_in = ''
	;

Solution 8 - Sql Server

Based on the previous answers, I have added it as a stored procedure to simplify the deletion of a column when it has attached constraints

CREATE OR ALTER PROC DROP_COLUMN(@TableName nvarchar(200), @ColumnName nvarchar(200))
AS 
BEGIN 
	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)
	EXEC('ALTER TABLE '+@TableName+' DROP COLUMN IF EXISTS ' + @ColumnName)
END

GO 
--example:
EXEC DROP_COLUMN N'VEHICLES', N'SCMT'
EXEC DROP_COLUMN N'VEHICLES', N'SSC'
EXEC DROP_COLUMN N'VEHICLES', N'RS'
EXEC DROP_COLUMN N'VEHICLES', N'RCEC'
 
DROP PROCEDURE IF EXISTS DROP_COLUMN

Solution 9 - Sql Server

I have updated script a little bit to my SQL server version

DECLARE @sql nvarchar(max)

SELECT @sql = 'ALTER TABLE `table_name` DROP CONSTRAINT ' + df.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
where t.name = 'table_name' and c.name = 'column_name'

EXEC sp_executeSql @sql
GO

ALTER TABLE table_name
  DROP COLUMN column_name;
Categories
Recommended Sql Server Solutions
Recommended Sql Server-2008 Solutions

Previous Solution:

How do I focus on one spec in jasmine.js?

Jasmine

Next Solution:

What is the purpose of the "final" keyword in C++11 for functions?

C++C++11Final

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionRobin Michael PoothuraiView Question on Stackoverflow
Solution 1 - Sql ServerOleg DokView Answer on Stackoverflow
Solution 2 - Sql ServerChris HalcrowView Answer on Stackoverflow
Solution 3 - Sql ServerMartin SmithView Answer on Stackoverflow
Solution 4 - Sql Servermarc_sView Answer on Stackoverflow
Solution 5 - Sql ServerBrainSlugs83View Answer on Stackoverflow
Solution 6 - Sql ServerEwald StiegerView Answer on Stackoverflow
Solution 7 - Sql ServerDataMatthisView Answer on Stackoverflow
Solution 8 - Sql ServerAdaptabiView Answer on Stackoverflow
Solution 9 - Sql ServerŁukasz Dawid WątorView Answer on Stackoverflow