Remove/Change default constraints sql 2000/2005

Leave a Comment / Uncategorized / By greg

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

Leave a Comment Cancel Reply