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:
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]
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.