How to Drop SQL Default Constraint Without Knowing Its Name

How to drop SQL default constraint without knowing its name?

Expanding on Mitch Wheat's code, the following script will generate the command to drop the constraint and dynamically execute it.

declare @schema_name nvarchar(256)
declare @table_name nvarchar(256)
declare @col_name nvarchar(256)
declare @Command  nvarchar(1000)

set @schema_name = N'MySchema'
set @table_name = N'Department'
set @col_name = N'ModifiedDate'

select @Command = 'ALTER TABLE ' + @schema_name + '.[' + @table_name + '] DROP CONSTRAINT ' + d.name
 from sys.tables t
  join sys.default_constraints d on d.parent_object_id = t.object_id
  join sys.columns c on c.object_id = t.object_id and c.column_id = d.parent_column_id
 where t.name = @table_name
  and t.schema_id = schema_id(@schema_name)
  and c.name = @col_name

--print @Command

execute (@Command)

DROP Constraint without knowing the name

-- Table T1
IF OBJECT_ID('dbo.T1') IS NOT NULL
    DROP TABLE T1

CREATE TABLE T1 (COL1 INT PRIMARY KEY)

-- Table T2
IF OBJECT_ID('dbo.T2') IS NOT NULL
    DROP TABLE T2

CREATE TABLE T2 (
    COL1 INT FOREIGN KEY REFERENCES T1(COL1)
    ,COL2 VARCHAR(2) UNIQUE
    ,COL3 INT NOT NULL DEFAULT(0)
    ,CHECK (
        COL3 IN (
            0
            ,1
            ,2
            )
        )
    )
GO

-- *** Foreign key constraint ***

DECLARE @Table NVARCHAR(256) = N'T2'
DECLARE @Column NVARCHAR(256) = N'COL1'
DECLARE @Command NVARCHAR(1000)

SELECT @Command = 'ALTER TABLE ' + '[' + @Table + '] DROP CONSTRAINT ' + CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = @Table
    AND COLUMN_NAME = @Column

--PRINT (@Command)  -- ALTER TABLE [T2] DROP CONSTRAINT FK__T2__COL1__058EC7FB
EXECUTE (@Command)

GO

-- *** Primary key constraint ***

DECLARE @Table NVARCHAR(256) = N'T1'
DECLARE @Command NVARCHAR(1000)

SELECT @Command = 'ALTER TABLE ' + '[' + @Table + '] DROP CONSTRAINT ' + [name]
FROM sys.key_constraints
WHERE [type] = 'PK'
    AND [parent_object_id] = OBJECT_ID(@table);

--PRINT (@Command)  -- ALTER TABLE [T1] DROP CONSTRAINT PK__T1__AA1D004EBAE57D94
EXECUTE (@Command)

GO

-- *** Unique key constraint ***

DECLARE @Table NVARCHAR(256) = N'T2'
DECLARE @Column NVARCHAR(256) = N'COL2'
DECLARE @Command NVARCHAR(1000)

SELECT @Command = 'ALTER TABLE [' + @Table + '] DROP CONSTRAINT ' + d.name
FROM sys.tables t
JOIN sys.indexes d ON d.object_id = t.object_id
    AND d.type = 2
    AND d.is_unique = 1
JOIN sys.index_columns ic ON d.index_id = ic.index_id
    AND ic.object_id = t.object_id
JOIN sys.columns c ON ic.column_id = c.column_id
    AND c.object_id = t.object_id
WHERE t.name = @Table
    AND c.name = @Column

--PRINT (@Command)  -- ALTER TABLE [T2] DROP CONSTRAINT UQ__T2__AA1D0040A435D4E0
EXECUTE (@Command)

GO

-- *** Not Null constraint ***

-- ALTER TABLE T2 ALTER COLUMN COL3 INT NULL

-- GO

DECLARE @Table NVARCHAR(256) = N'T2'
DECLARE @Column NVARCHAR(256) = N'COL3'
DECLARE @Command NVARCHAR(1000)

SELECT @Command = 'ALTER TABLE ' + '[' + @Table + '] DROP CONSTRAINT ' + d.name
FROM sys.tables t
JOIN sys.default_constraints d ON d.parent_object_id = t.object_id
JOIN sys.columns c ON c.object_id = t.object_id
    AND c.column_id = d.parent_column_id
WHERE t.name = @Table
    AND c.name = @Column

--PRINT (@Command)  -- ALTER TABLE [T2] DROP CONSTRAINT DF__T2__COL3__0682EC34
EXECUTE (@Command)

GO

-- *** Check constraint ***

DECLARE @Table NVARCHAR(256) = N'T2'
DECLARE @Column NVARCHAR(256) = N'COL3'
DECLARE @Command NVARCHAR(1000)

SELECT @Command = 'ALTER TABLE ' + '[' + @Table + '] DROP CONSTRAINT ' + d.name
FROM sys.tables t
JOIN sys.check_constraints d ON d.parent_object_id = t.object_id
JOIN sys.columns c ON c.object_id = t.object_id
    AND c.column_id = d.parent_column_id
WHERE t.name = @Table
    AND c.name = @Column

--PRINT (@Command)  -- ALTER TABLE [T2] DROP CONSTRAINT CK__T2__COL3__0777106D
EXECUTE (@Command)

Some of the queries are from my local repository and some are taken references from different sources:

Finding a Primary Key Constraint on the fly in SQL Server 2005

Drop Foreign Key without knowing the name of the constraint?

How to drop a unique constraint from table column?

Drop constraint on a column without knowing constraint name

you can retrieve constraint information from a table like this

select t.Name as TableName,
       ccd.Name as ColumnName_default_constraint,
       dc.Name as default_constraint,
       ccc.Name as ColumnName_check_constraint,
       cc.Name as check_constraint
from   sys.tables t
  inner join sys.default_constraints dc on t.object_id = dc.parent_object_id
  inner join sys.check_constraints cc on t.object_id = cc.parent_object_id
  inner join sys.columns ccd on dc.parent_object_id = ccd.object_id 
                          and ccd.column_id = dc.parent_column_id
  inner join sys.columns ccc on cc.parent_object_id = ccc.object_id 
                          and ccc.column_id = cc.parent_column_id
where t.Name = 'your table name'
order by t.Name

from this you can build scripts like this

select 'alter table ' + t.Name +' drop constraint ' + dc.Name,
       'alter table ' + t.Name +' drop constraint ' + cc.Name
from   sys.tables t
  inner join sys.default_constraints dc on t.object_id = dc.parent_object_id
  inner join sys.check_constraints cc on t.object_id = cc.parent_object_id
where t.Name = 'your table name'
order by t.Name

Drop default constraint on a column in TSQL

This is how you would drop the constraint

ALTER TABLE <schema_name, sysname, dbo>.<table_name, sysname, table_name>
   DROP CONSTRAINT <default_constraint_name, sysname, default_constraint_name>
GO

With a script

-- t-sql scriptlet to drop all constraints on a table
DECLARE @database nvarchar(50)
DECLARE @table nvarchar(50)

set @database = 'dotnetnuke'
set @table = 'tabs'

DECLARE @sql nvarchar(255)
WHILE EXISTS(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_catalog = @database and table_name = @table)
BEGIN
    select    @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + CONSTRAINT_NAME 
    from    INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
    where    constraint_catalog = @database and 
            table_name = @table
    exec    sp_executesql @sql
END

Credits go to Jon Galloway
http://weblogs.asp.net/jgalloway/archive/2006/04/12/442616.aspx

How to check whether a default constraint with no name exist on a column

A column can only have one default constraint on it.

You can find the details for any such default constraint with the below query against sys.default_constraints.

DECLARE @TableName NVARCHAR(500) = N'dbo.Test', 
        @ColumnName sysname = N'CreatedOnDate';

SELECT d.definition,
       d.is_system_named, /* 1 if no name given by the user and was allocated a system generated one */
       d.name
FROM   sys.default_constraints AS d
       INNER JOIN sys.columns AS c
               ON d.parent_object_id = c.object_id
                  AND d.parent_column_id = c.column_id
WHERE  d.parent_object_id = OBJECT_ID(@TableName, N'U')
       AND c.name = @ColumnName; 

NB: If you later need to re-create the default constraint (perhaps to reference SYSUTCDATETIME instead of GETDATE) the constraint name created by your current CREATE TABLE will be different in all environments and you will have to resort to dynamic SQL to do this. It is advisable to always name your constraints (for permanent tables).

---

Related Topics