If you've worked with SQL Server for a while, you're familiar with the syntax:
ALTER TABLE table_name
DROP COLUMN column_name
Easy enough, right? But if you have a default constraint on that column, you need to drop the constraint before you can drop the column.
If you originally created the constraint & specified the name, something like this:
ALTER TABLE table_name
ADD column_name bit NOT NULL
CONSTRAINT DF__table_name__column_name DEFAULT (0)
then you simply drop the constraint, then drop the column. Easy enough.
However, if you did not explicitly name the constraint, the system-generated name might be something like "DF__table_nam__colum__51EF2864" so you would have to go look up the name. If you are doing some clean-up, removing a number of columns, that can take some time.
The good news is that you can run some code to get the constraint name & drop it, then drop the column, like this:
CREATE PROC dbo.wmAlterTableDropColumn
(
@table_name sysname,
@column_name sysname
)
AS
DECLARE @constraint sysname, @sql nvarchar(4000)
IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(@table_name) AND [name] = @column_name)
BEGIN
SELECT
@constraint = dc.name
FROM
sys.tables t
INNER JOIN sys.columns c
ON t.object_id = c.object_id
INNER JOIN sys.default_constraints dc
ON t.object_id = dc.parent_object_id
AND c.column_id = dc.parent_column_id
WHERE
t.name = @table_name
AND c.name = @column_name
IF @constraint IS NOT NULL
BEGIN
SET @sql = 'ALTER TABLE ' + @table_name + ' DROP CONSTRAINT '+ @constraint
EXEC sp_executesql @sql
END
SET @sql = 'ALTER TABLE ' + @table_name + ' DROP COLUMN ' + @column_name
EXEC sp_executesql @sql
END
GO
...and then to use this for our example, you would run this:
EXEC dbo.wmAlterTableDropColumn 'table_name', 'column_name'
