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
			FROM		sys.objects so 
			JOIN		sys.sysconstraints sc
			ON			so.object_id = sc.constid
			WHERE		so.parent_object_id = object_id(@tablename)
						AND so.type = 'D'
						AND sc.colid = (
											SELECT		colid 
											FROM		syscolumns
											WHERE		id = 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'
					SELECT		sc.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
					FROM		sys.objects so 
					JOIN		sys.sysconstraints sc
					ON			so.object_id = sc.constid
					JOIN		sys.schemas sch
					ON			sch.schema_id = so.schema_id
					WHERE		1=1
								AND so.type = 'D'
					ORDER BY	sch.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
				FROM		sys.objects so 
				JOIN		sys.sysconstraints sc
				ON			so.object_id = sc.constid
				WHERE		so.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