How to generate Scripts to Add Default Constraints to Column in Multiple Tables in SQL Server Database - SQL Server / TSQL Tutorial Part 94

Scenario:

You are working as SQL Server Developer, your company has the database with tons of tables. Each Table has some audit columns such as CreatedOn, CreatedBy. No default Constraints were ever created on them. You want to create the Default Constraint on CreatedOn Column for all the table with value getdate(), also you want to create Default Constraint for CreatedBy=SUSER_SNAME().
You may have another column of type string and you would like to generate scripts for that too.

Solution:

The below script can be used to generate Default Constraint for given column in entire database for all the tables. The name for Default Constraint will be DF_SchemaName_TableName_ColumnName.

Let's generate Default Constraint scripts for CreatedOn column with value Getdate() by using below script.


Declare @ColumnName VARCHAR(128)
Declare @DefaultValue VARCHAR(100)
SET @ColumnName='CreatedON'
SET @DefaultValue='Getdate()'

Select Schema_Name(Schema_id) as SchemaName,
t.Name as TableName,
C.Name as ColumnName,
'Alter Table ['+Schema_Name(Schema_id)
+'].['+t.Name+']'
+' Add Constraint DF_'+Schema_Name(schema_id)
    +'_'+t.name
    +'_'+c.name+''
    +' default '+@DefaultValue+' for '
    + @ColumnName as CreateDefaultConstraint
from sys.tables t
inner join sys.columns c
on t.object_id=c.object_id
and t.is_ms_shipped=0
and c.name=@ColumnName


Generate Scripts to Add Default Constraint to Column in Database in SQL Server

You only have to make change to @ColumnName and @DefaultValue variables as per your requirements. Let's use the same script to add Default Constraint to CreatedBy Column with value=SUSER_SNAME().

Declare @ColumnName VARCHAR(128)
Declare @DefaultValue VARCHAR(100)
SET @ColumnName='CreatedBy'
SET @DefaultValue='SUSER_SNAME()'

Select Schema_Name(Schema_id) as SchemaName,
t.Name as TableName,
C.Name as ColumnName,
'Alter Table ['+Schema_Name(Schema_id)
+'].['+t.Name+']'
+' Add Constraint DF_'+Schema_Name(schema_id)
    +'_'+t.name
    +'_'+c.name+''
    +' default '+@DefaultValue+' for '
    + @ColumnName as CreateDefaultConstraint
from sys.tables t
inner join sys.columns c
on t.object_id=c.object_id
and t.is_ms_shipped=0
and c.name=@ColumnName

How to generate script to add  Default Constraint on column in SQL Server Database


Now let's consider that you want to set default value to some string value as we want to create Default Constraint for Region column='USA' , When set the value of @DefaultValue variable, add single quotes as shown below.

Declare @ColumnName VARCHAR(128)
Declare @DefaultValue VARCHAR(100)
SET @ColumnName='Region'
SET @DefaultValue='''USA'''

Select Schema_Name(Schema_id) as SchemaName,
t.Name as TableName,
C.Name as ColumnName,
'Alter Table ['+Schema_Name(Schema_id)
+'].['+t.Name+']'
+' Add Constraint DF_'+Schema_Name(schema_id)
    +'_'+t.name
    +'_'+c.name+''
    +' default '+@DefaultValue+' for '
    + @ColumnName as CreateDefaultConstraint
from sys.tables t
inner join sys.columns c
on t.object_id=c.object_id
and t.is_ms_shipped=0
and c.name=@ColumnName


How to generate scripts to add Default Constraint on Column in multiple Tables in SQL Server Database



Video Demo : Generate Scripts to add Default Constraint to Column in Multiple Tables in SQL Server Database


No comments:

Post a Comment