How do you drop a default value or similar constraint in T-SQL?
If you want to do this manually, you can use Management Studio to find it (under the Constraints node inside the table).
To do it using SQL:
If the constraints are default constraints, you can use
sys.default_constraints
to find it:SELECT OBJECT_NAME(parent_object_id) AS TableName, name AS ConstraintName
FROM sys.default_constraints ORDER BY TableName, ConstraintNameIf you are looking for other constraints as well (check, unique, foreign key, default, primary key), you can use
sysconstraints
:SELECT OBJECT_NAME(id) AS TableName, OBJECT_NAME(constid) AS ConstraintName
FROM sysconstraints ORDER BY TableName, ConstraintName
You do not say which version of SQL Server you are using. The above work on both SQL 2005 and SQL 2008.
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 do you drop a default value from a column in a table?
Its a default constraint, you need to perform a:
ALTER TABLE {TableName}
DROP CONSTRAINT ConstraintName
If you didn't specify a name when you created the constraint, then SQL Server created one for you. You can use SQL Server Management Studio to find the constraint name by browsing to the table, opening its tree node, then opening the Constraints node.
If I remember correctly, the constraint will be named something along the lines of DF_SomeStuff_ColumnName.
EDIT: Josh W.'s answer contains a link to a SO question that shows you how to find the auto generated constraint name using SQL instead of using the Management Studio interface.
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)
How can I delete or alter a default constraint from table in SQL Server database for multiple database?
Run this and output as text, then copy the result and run in another query window
Fritz with the code below to ADD the updated constraint:
;With cte As (select object_id From sys.objects where name like 'DF__Documents__ShowO%')
Select 'Alter Table ' + Object_Name(df.object_id) + N' Add Constraint Default (1) For ShowOnHandset'
From sys.default_constraints As df
Join cte As c
On c.object_id = df.object_id
This deletes the constraints
;With cte As (select object_id From sys.objects where name like 'DF__Documents__ShowO%')
Select 'Alter Table ' + Object_Name(df.object_id) + N' Drop Constraint [' + df.Name + ']'
From sys.default_constraints As df
Join cte As c
On c.object_id = df.object_id
altering DEFAULT constraint on column SQL Server
When you add a default, you should use names for your constraints. This way you can later refer to those constraints by name.
ALTER TABLE [dbo].[PMIPatients] ADD CONSTRAINT [PatientFallRiskLevel_Default] DEFAULT ((0)) FOR PatientFallRiskLevel
Then you can drop it using:
ALTER TABLE [dbo].[PMIPatients] DROP CONSTRAINT [PatientFallRiskLevel_Default]
How to drop column with constraint?
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
Related Topics
Update Query Using Subquery in SQL Server
Inserting into Oracle and Retrieving the Generated Sequence Id
How to Start Auto Increment from a Specific Point
Union All VS or Condition in SQL Server Query
Hql: How to Perform an Inner Join on a Subquery
Concurrency Handling of SQL Transactrion
Differencebetween a Stored Procedure and a View
To Ignore Duplicate Keys During 'Copy From' in Postgresql
How to Quickly Edit Values in Table in SQL Server Management Studio
How to Create an Oracle Sequence Starting with Max Value from a Table
Oracle 'Partition By' and 'Row_Number' Keyword
Show All Rows That Have Certain Columns Duplicated
Why Don't Dbms's Support Assertion
Prepared Statement on Postgresql in Rails
How to Measure the Execution Time of a Query on Spark
Postgresql: Give All Permissions to a User on a Postgresql Database
What Is the Most Appropriate Data Type for Storing an Ip Address in SQL Server