How to drop all Default Constraints in SQL Server Database - SQL Server / TSQL Tutorial Part 94

Scenario:

You are working as SQL Server Developer, you need to generate scripts to drop all the Default Constraints in SQL Server Database.

Solution:

To drop Default Constraint, we use below syntax

Alter Table [SchemaName].[TableName]

Drop Constraint [Constraint Name]

Below query can be used to generate scripts to generate all Default Constraints in SQL Server Database.

USE YourDatabaseName
go
SELECT
    DB_Name() AS DBName,
    Schema_name(t.Schema_id)AS SchemaName, 
    t.name AS TableName, 
    c.name AS ColumnName, 
    d.name AS DefaultConstraintName, 
    d.definition AS DefaultDefinition,
    'Alter table ['+Schema_name(t.Schema_id)+'].['
    +t.name+'] Drop Constraint ['+d.name+']' as DropDefaultConstraintQuery
FROM sys.default_constraints d
INNER JOIN sys.columns c ON
    d.parent_object_id = c.object_id
    AND d.parent_column_id = c.column_id
INNER JOIN sys.tables t ON
    t.object_id = c.object_id

If you want to exclude some tables or schema, you can further filter the records by using where clause in query. 

I executed above query and it generated drop scripts for all Default Constraints.

How to generate scripts to Drop all Default Constraints in SQL Server Database

Take the results from DropDefaultConstraint column and execute to drop Default Constraints.

Video Demo : How to drop all Default Constraints in SQL Server Database