How to remove default value or similar restriction in T-SQL?

Question

I know the syntax:

ALTER TABLE [TheTable] DROP CONSTRAINT [TheDefaultConstraint] 

but how to reset the default limit when I do not know his name? (That is, it was auto-generated over time by CREATE TABLE .)

Answer 1

If you want to do this manually, you can use Management Studio to find it (under the Constraints node inside the table).

To do this using SQL:

• If the constraints are the 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, ConstraintName 

• If you are looking for other restrictions (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 is done for both SQL 2005 and SQL 2008.

Answer 2

You can use this code to do this automatically:

 DECLARE @tableName VARCHAR(MAX) = '<MYTABLENAME>' DECLARE @columnName VARCHAR(MAX) = '<MYCOLUMNAME>' DECLARE @ConstraintName nvarchar(200) SELECT @ConstraintName = Name FROM SYS.DEFAULT_CONSTRAINTS WHERE PARENT_OBJECT_ID = OBJECT_ID(@tableName) AND PARENT_COLUMN_ID = ( SELECT column_id FROM sys.columns WHERE NAME = @columnName AND object_id = OBJECT_ID(@tableName)) IF @ConstraintName IS NOT NULL EXEC('ALTER TABLE '+@tableName+' DROP CONSTRAINT ' + @ConstraintName) 

Just replace <MYTABLENAME> and <MYCOLUMNNAME> accordingly.

Answer 3

You can find the name of the constraint using sp_help [table name], and then drop it by name.

Or you can do it through a management studio.

Answer 4

Or you can find it using the sys.check_constraints directory view.

Answer 5

For one table and column in one row, use the following

 declare @sql nvarchar(max); set @sql = ''; SELECT @sql+='ALTER TABLE [dbo].[YOURTABLENAME] DROP CONSTRAINT ' + ((SELECT OBJECT_NAME(constid) FROM sysconstraints WHERE OBJECT_NAME(id) = 'YOURTABLENAME'AND colid IN (SELECT ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS Where Table_Name = 'YOURTABLENAME' and COLUMN_NAME = 'YOURCOLUMNNAM'))) + ';'; EXEC sp_executesql @sql; 

If you have several restrictions on a column, you will need to distinguish between the restriction that you are behind, but if you have a default restriction, this will do the trick.

Check out the other columns available in the info_schematic so that you can distinguish further.

Answer 6

Here comes my own version, which reduces all dependent restrictions - the default restriction (if exists) and all affected control restrictions (as the SQL standard seems to suggest, and how some other databases look like this)

 declare @constraints varchar(4000); declare @sql varchar(4000); with table_id_column_position as ( select object_id table_id, column_id column_position from sys.columns where object_id is not null and object_id = object_id('TableName') and name = 'ColumnToBeDropped' ) select @constraints = coalesce(@constraints, 'constraint ') + '[' + name + '], ' from sysobjects where ( -- is CHECK constraint type = 'C' -- dependeds on the column and id is not null and id in ( select object_id --, object_name(object_id) from sys.sql_dependencies, table_id_column_position where object_id is not null and referenced_major_id = table_id_column_position.table_id and referenced_minor_id = table_id_column_position.column_position ) ) OR ( -- is DEFAULT constraint type = 'D' and id is not null and id in ( select object_id from sys.default_constraints, table_id_column_position where object_id is not null and parent_object_id = table_id_column_position.table_id and parent_column_id = table_id_column_position.column_position ) ); set @sql = 'alter table TableName drop ' + coalesce(@constraints, '') + ' column ColumnToBeDropped'; exec @sql 

(Beware: both TableName and ColumnToBeDropped appear twice in the code above)

This works by building a single ALTER TABLE TableName DROP CONSTRAINT c1, ..., COLUMN ColumnToBeDropped and executing it.