How to drop Default Constraint on a Table Column without knowing Constraint Name in MS SQL Server 2005
SQL developers create and drop default constraint on a database table column frequently. If the T-SQL programmers wants to drop a table column where a default constraint is defined, first the default constraint should be dropped. In order to drop default constraint on a column, SQL developers need to know the table and constraint name to use following Transact-SQL command:
ALTER TABLE sqlTableName DROP CONSTRAINT sqlConstraintName
Although it is a line of code to execute for deleting a SQL Server default constraint, if you do not know the name of the SQL constraint name it might be a problem. Especially if you create scripts to run unattendant, you must know the name of the SQL constraint somehow to build your SQL batches.
Following SQL Server stored procedure finds the default constraint name created on a table column and drop default constraint that is found. The "ALTER TABLE DROP CONSTRAINT" command is created dynamically and executed using the SQL sp_executesql stored procedure to delete default constraint.
CREATE PROC SP_DROP_Default_Constraint
(
@table_name sysname,
@column_name sysname
)
AS
--declare @table_name sysname, @column_name sysname,
--select
-- @table_name = N'SampleConstraintsSQLTable',
-- @column_name = N'IsDefaultConstraintColumn'
declare @default_constraint_name sysname, @sql nvarchar(max)
if exists (
select *
from sys.default_constraints
where
parent_object_id = OBJECT_ID(@table_name)
AND type = 'D'
AND parent_column_id = (
select column_id
from sys.columns
where
object_id = OBJECT_ID(@table_name)
and name = @column_name
)
)
begin
select @default_constraint_name = name
from sys.default_constraints
where
parent_object_id = OBJECT_ID(@table_name)
AND type = 'D'
AND parent_column_id = (
select column_id
from sys.columns
where
object_id = OBJECT_ID(@table_name)
and name = @column_name
)
SET @sql = N'ALTER TABLE ' + @table_name + ' DROP Constraint ' + @default_constraint_name
exec sp_executesql @sql
end
GO
After default constraint is dropped, SQL programmers can drop the table column unless any other SQL constraints are not defined on target column. For example if a SQL Check constraint if defined, using a similar stored procedure developers or database administrators can also drop check constraint defined on table column without knowing constraint name.