How to Drop Default Constraints in SQL Server When Their Names Are Unknown?

Dropping Default Constraints with Unknown Names in Microsoft SQL Server

This question addresses the challenge of removing default constraints from SQL tables when their names are unknown, due to potential typos. The existing query, as provided by the user, requires the exact constraint name for execution, which can be problematic in certain situations.

The Solution

The suggested solution involves generating and executing a dynamic SQL command to drop the constraint based on the table, column, and schema information provided. The code snippet shared by the user can be expanded upon as follows:

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)

This code generates the necessary SQL command to drop the default constraint for a specific table and column in a particular schema. The command is dynamically constructed based on the provided parameters, ensuring it can be executed without any SQL errors even if the constraint name is unknown.

The above is the detailed content of How to Drop Default Constraints in SQL Server When Their Names Are Unknown?. For more information, please follow other related articles on the PHP Chinese website!