How to rename all Default Constraints according to Naming Standards or naming Convention in SQL Server - SQL Server / TSQL Tutorial Part 93

Scenario:

You are working as SQL Server developer, You need to prepare the scripts those can be used to rename Default Constraints as per your company's standards. Your company want to name the default constraints e.g 'DF_SchemaName_TableName_ColumnName'

Solution:

We can use sp_rename Stored Procedure to rename Default Constraints. To rename single Default Constraint we can use script like below

Exec sp_rename 'Current_Default_Constraint_Name','New Default Constraint Name'

as we need to run this script for all the default constraints, we can use below query to generate rename Default Constraints.

 SELECT 'exec sp_rename '''
    +Schema_name(d.Schema_id)+'.' 
    + '' + d.Name + ''''
    + ',''DF_' +Schema_Name(d.schema_id)
    +'_'+t.name
    +'_'+c.name+'''' as RenameDefaultConstraintQuery
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

I execute above query on one of the database and I got below results. If you want to exclude some tables you can always filter them in where clause.

How to rename all Default Constraints in SQL Server Database according to Naming Convention

Video Demo : How to Rename all default Constraints according to naming conventions in SQL Server Database