Problem
When we add default constraint on a field it is automatically assigned a name in following format
Format:
DF__tablename__PARTOFFIELDNAME__HASHCODE
Example:
DF__scheduled__CREAT__00DF2177
DF__scheduled__MODIF__01D345B0
HashCode part of the format is different on each database so we can’t just find the name of constraint and use that in our alter script. That’s when sysobjects table comes to the rescue.
This will list all the default field values for all tables
SELECT OBJECT_NAME(ID) AS NameofConstraint,
OBJECT_NAME(parent_obj) AS TableName
FROM sysobjects
WHERE xtype = 'D'
from here we can construct our TSQL script to suit our needs.
Solution
- Get default fields of interes t(ConstraintName, TableName)
- Drop each constraint
- Add new default constraint with a NAME
/**
@Desc: Remove default constraints from a given table, then add new default constraint
2000/2005 compatible
@Author Greg B.
**/
USE [mydb]
GO
BEGIN TRANSACTION
GO
Declare MyCursor Cursor For
SELECT OBJECT_NAME(ID) AS NameofConstraint,
OBJECT_NAME(parent_obj) AS TableName
FROM sysobjects
WHERE xtype = 'D'
AND (OBJECT_NAME(parent_obj) = 'procedures' AND OBJECT_NAME(ID) LIKE '%DF__procedure__INHER%')
OR
(
OBJECT_NAME(parent_obj) = 'subarea'
AND (OBJECT_NAME(ID) LIKE '%DF__subarea__ABS_INH__%')
OR (OBJECT_NAME(ID) LIKE '%DF__subarea__ASSIGNE__%')
)
DECLARE @SQLScript NVARCHAR(300)
Declare @NameofConstraint VARCHAR(255)
Declare @SchemaName VARCHAR(255)
Declare @TableName VARCHAR(255)
Declare @ConstraintType VARCHAR(255)
Open MyCursor
Declare @Count int
Select @Count = 0
FETCH NEXT FROM MyCursor INTO @NameofConstraint, @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
--PINT 'RECORD='+@NameofConstraint +' :: '+@TableName
Select @SQLScript= 'ALTER TABLE '+ @TableName +' DROP CONSTRAINT '+@NameofConstraint
EXEC sp_executesql @SQLScript
Select @Count=@Count+1
--Advance to next record
FETCH NEXT FROM MyCursor INTO @NameofConstraint,@TableName
END
Close MyCursor
DEALLOCATE MyCursor
if @Count != 0
BEGIN
print 'Adding alter'
-- Now we add the constrains back again to the tables with standarized names
ALTER TABLE [dbo].[procedures] ADD CONSTRAINT DF_PROCEDURE_INHERENT_RISK DEFAULT ((1)) FOR [INHERENT_RISK]
ALTER TABLE [dbo].[subarea] ADD CONSTRAINT DF_SUBAREA_ABS_INHERENT_RISK DEFAULT ((1)) FOR [ABS_INHERENT_RISK]
ALTER TABLE [dbo].[subarea] ADD CONSTRAINT DF_SUBAREA_ASSIGNED_INHERENT_RISK DEFAULT ((1)) FOR [ASSIGNED_INHERENT_RISK]
END
COMMIT TRANSACTION
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRAN
END
GO