Clean up default constraints

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