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

7 comments:

  1. I am very happy to discover your post as it will become on top in my collection of favorite blogs to visit. brand names on sale

    ReplyDelete
  2. nice post, keep up with this interesting work. It really is good to know that this topic is being covered also on this web site so cheers for taking time to discuss this! rebrand

    ReplyDelete
  3. You’ve got some interesting points in this article. I would have never considered any of these if I didn’t come across this. Thanks!. name design company

    ReplyDelete
  4. Hey, this day is too much good for me, since this time I am reading this enormous informative article here at my home. Thanks a lot for massive hard work. creative brand names

    ReplyDelete
  5. Hi! Thanks for the great information you havr provided! You have touched on crucuial points! rolet online

    ReplyDelete
  6. I believe other website owners should take this site as an example, very clean and amazing style, and has a user-friendly design.
    Visit: Accounting Affiliate Program

    ReplyDelete
  7. It's great to know a lot about your site's blog. Good article. I hope that the guide on your blog will be useful to me.
    att email not working

    ReplyDelete