SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2017
download SQL Server 2016
download SQL Server 2014

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.






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems