Create script for Default Constraint in SQL Server

Create script for Default Constraint in SQL Server

We have Production and Beta database as every project suppose to have but due to some issues we have lost some of the default constraint in Beta database so I had to find which constraint was missed. I have few ways to do this either I check manually or I use any 3rd party tools or visual studio to compare schema or use system table & DMV. I decided to go for system tables and DMV. I have quickly created following TSQL script which I ran in both databases, make join to found which constraints are missing and then finally copy Constraint script from following query and created those constraints in Beta database.

Here is the TSQL script I have used:

[sourcecode language=”sql”]SELECT AS ConstraintName,

schema_name(Tab.schema_id) AS SchemaName,

OBJECT_NAME(DefCons.parent_object_id) AS TableName, AS ColumnName,

DefCons.definition AS ConstraintValue,

‘ALTER TABLE [‘ + schema_name(Tab.schema_id) + ‘].[‘ + OBJECT_NAME(DefCons.parent_object_id)


+ DefCons.definition + ‘ FOR [‘ + + ‘]’ AS ConstraintScript


sys.default_constraints AS DefCons


sys.columns AS Col


DefCons.parent_object_id = Col.object_id AND DefCons.parent_column_id = Col.column_id


sys.tables Tab



If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

Note: Microsoft Books online is a default reference of all articles.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

1 thought on “Create script for Default Constraint in SQL Server”

Comments are closed.