Problem Statement: My Physical model is for SQL Server, all Default Constraint didn't had a name on them. This made managing constraints a nightmare during deployment.
Steps to Prepare deployment
Step 1: Have a script that removes all Default constraints from the DB. Include the script "01.1_pretables.sql" as part of your pre-deployment script to drop all Default Constraints.
-- #### at the end delete all Default constraint and create new using ER Studio with name ####
SELECT ID = ROW_NUMBER() OVER(ORDER BY object_id ASC)
, TABLE_NAME = OBJECT_NAME([object_id])
, CONSTRAINT_NAME = OBJECT_NAME([default_object_id])
, EXECUTE_STATEMENT = 'ALTER TABLE [' + OBJECT_NAME([object_id]) + '] DROP CONSTRAINT [' + OBJECT_NAME([default_object_id]) + ']'
WHERE default_object_id <> 0
AND OBJECT_NAME([default_object_id]) <> 'One'
SELECT * FROM #TEMP_RESULTS
DECLARE @maxval int, @ind INT = 1;
DECLARE @Query nvarchar(2000)
SELECT @maxval = MAX(ID) FROM #TEMP_RESULTS;
while (@ind <= @maxval)
SET @Query = ''
select @Query = EXECUTE_STATEMENT from #TEMP_RESULTS where ID=@ind;
SET @ind = @ind+1;
DROP TABLE #TEMP_RESULTS
Step 2: Once you have executed the above SQL script on the DB. Then use the Macro "RenameDFConstraintsV0.3.BAS" to rename all Default constraint on the ER Studio Physical Model for SQL Server.RenameDFConstraintsV0.3.zip
Step 3: Compare Physical model with the DB and create Deployment script to take care of all DF constraints.
I keep using the Macro on regular basis to rename new DF constraint create in the Table design.
The macro name format is DF_<table name>_<column name>
A file named "UpdatedDFKeyInfo.txt" is generated next to your ER Studio data Architect Modle File (.dm1). This file gives more info of all the activities those were carried out during the rename process (like, table name and column name: Default Constraint name, Default value).
Thanks for sharing!