Differences

This shows you the differences between two versions of the page.

Link to this comparison view

tsql:drop_default_constraint [2017/07/21 21:47] (current)
Line 1: Line 1:
 +====== Drop default constraint ======
 +
 +This script identifies a column'​s default constraint (if any) and drops it:
 +
 +<code tsql>​DECLARE @sql NVARCHAR(MAX)
 +
 +SELECT ​
 +  @sql = 'ALTER TABLE [' + s.name + '​].['​ + o.name + ']
 +DROP CONSTRAINT [' + dc.name + '​]'​
 +FROM sys.schemas s
 +  INNER JOIN sys.objects o ON o.schema_id = s.schema_id
 +  INNER JOIN sys.columns c ON c.object_id = o.object_id
 +  INNER JOIN sys.default_constraints dc 
 +    ON dc.parent_object_id = o.object_id
 +    AND dc.parent_column_id = c.column_id
 +WHERE s.name = '​schemaName'​
 +AND o.name = '​tableName'​
 +AND c.name = '​columnName'​
 +
 +PRINT @sql
 +EXEC(@sql)
 +</​code>​
 +
 +{{tag> tsql dynamic development}}
 +~~DISCUSSION~~
 +