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

DefCons.name AS ConstraintName,

schema_name(Tab.schema_id) AS SchemaName,

OBJECT_NAME(DefCons.parent_object_id) AS TableName,

Col.name AS ColumnName,

DefCons.definition AS ConstraintValue,

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

+’] ADD CONSTRAINT [‘+DefCons.name+’] DEFAULT ‘

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

FROM

sys.default_constraints AS DefCons

INNER JOIN

sys.columns AS Col

ON

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

INNER JOIN

sys.tables Tab

ON

Tab.object_id=Col.object_id][/sourcecode]

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

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

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.