Technical Article

Clean up default constraints

,

The script scrapes out all default constraints (optional for particular column, table or script generation for the whole db) in SQL Server 2005 manner.
Based on: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro2k/html/sql00a11.asp

IF OBJECT_ID ('usp_sys_CleanUpDefaultConstraintsEx')IS NOT NULL 
DROP PROCEDURE usp_sys_CleanUpDefaultConstraintsEx
GO

/*-------------------------------------------------------------------------

EXEC usp_sys_CleanUpDefaultConstraintsEx null, null, 1
EXEC usp_sys_CleanUpDefaultConstraintsEx null, null, 0
EXEC usp_sys_CleanUpDefaultConstraintsEx 'schema.tablename', null, 1
EXEC usp_sys_CleanUpDefaultConstraintsEx 'schema.tablename', null, 0
EXEC usp_sys_CleanUpDefaultConstraintsEx 'schema.tablename', 'columnname', 1
EXEC usp_sys_CleanUpDefaultConstraintsEx 'schema.tablename', 'columnname', 0

sp_help 'schema.tablename'
-------------------------------------------------------------------------*/ 

CREATE PROCEDURE usp_sys_CleanUpDefaultConstraintsEx(
@tablename VARCHAR(MAX)
, @columnname VARCHAR(MAX)
, @debug BIT)
AS
BEGIN
-- Clean up column 
DECLARE@defname VARCHAR (MAX)
, @cmd VARCHAR (MAX)

IF @columnname IS NOT NULL
BEGIN
IF (@debug = 1)
PRINT 'Status: Clean up column ' + @columnname + ' on table ' + @tablename
-- clean up column 
SELECT@defname = name
FROMsys.objects so 
JOINsys.sysconstraints sc
ONso.object_id = sc.constid
WHEREso.parent_object_id = object_id(@tablename)
AND so.type = 'D'
AND sc.colid = (
SELECTcolid 
FROMsyscolumns
WHEREid = object_id(@tablename) 
AND NAME = @columnname
)

SELECT@cmd='ALTER TABLE ' + @tablename + ' DROP CONSTRAINT ' + @defname
IF (LEN(@defname)>0)
BEGIN
IF (@debug = 1)
PRINT 'Command: ' + @cmd
EXEC (@cmd)
IF (@debug = 1)
PRINT 'Status: single command completed';
END
ELSE 
BEGIN
PRINT 'Status: No defaults found for that column ' + @columnname
END 
END 
ELSE
BEGIN
IF (@tablename IS NULL)
BEGIN 
--IF (@debug = 1)
--PRINT 'Status: Clean up the whole db'
SELECTsc.constid as ConstraintID
, so.name as ConstraintName
, object_name(so.parent_object_id) as TableName
, so.schema_id
, sch.Name AS SchemaName
, 'ALTER TABLE ' + sch.Name +'.' + object_name(so.parent_object_id) + ' DROP CONSTRAINT ' + so.name AS sql
FROMsys.objects so 
JOINsys.sysconstraints sc
ONso.object_id = sc.constid
JOINsys.schemas sch
ONsch.schema_id = so.schema_id
WHERE1=1
AND so.type = 'D'
ORDER BYsch.Name
, so.Name 
END 
ELSE 
BEGIN 
-- clean up the whole table
DECLARE@sql VARCHAR(MAX)
SET@cmd = 'ALTER TABLE ' + @tablename  + ' DROP CONSTRAINT '
-- gather all defaults for the particular table 
SELECT@sql = COALESCE (@sql + CHAR (13) + 'ALTER TABLE ' + @tablename + ' DROP CONSTRAINT ' , '') + name
FROMsys.objects so 
JOINsys.sysconstraints sc
ONso.object_id = sc.constid
WHEREso.parent_object_id = object_id(@tablename)
AND so.type = 'D'
SET@cmd = @cmd + @sql
IF (LEN(@cmd) > 0 )
BEGIN
IF (@debug = 1)
PRINT 'Status: Generating command list.'
PRINT 'Command list: '+ char(13) + @cmd
EXEC (@cmd)
IF (@debug = 1)
PRINT 'Status: command list completed';
END
ELSE 
BEGIN
IF (@debug = 1)
PRINT 'Status: No defaults found for that table'
END
END
END
END
GO

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating