IF EXISTS (SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID(N'[dbo].[DropConstraintsForColumn]'))
	DROP PROCEDURE [dbo].[DropConstraintsForColumn];
SEPARATOR
IF EXISTS (SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID(N'[dbo].[DropColumnWithConstraints]'))
	DROP PROCEDURE [dbo].[DropColumnWithConstraints];
SEPARATOR
IF EXISTS (SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID(N'[dbo].[AlterColumnDroppingItsConstraints]'))
	DROP PROCEDURE [dbo].[AlterColumnDroppingItsConstraints];
SEPARATOR


CREATE PROCEDURE [dbo].[DropConstraintsForColumn]
	@tableName varchar(128),
	@columnName varchar(128)
	AS SET NOCOUNT ON
	SET xact_abort ON

	WHILE 0=0 BEGIN
		DECLARE @defaultName varchar(128)
		SET @defaultName = (
		SELECT TOP 1 defaults.Name
			FROM sys.tables tab
			INNER JOIN sys.default_constraints defaults ON tab.object_id = defaults.parent_object_id
			INNER JOIN sys.columns col ON defaults.parent_object_id = col.object_id AND col.column_id = defaults.parent_column_id
			WHERE tab.Name = @tableName AND col.Name = @columnName)
		IF @defaultName IS NULL BREAK
		EXEC ('ALTER TABLE "'+@tableName+'" DROP CONSTRAINT "'+@defaultName+'"')
	END
	WHILE 0=0 BEGIN
		DECLARE @constraintName varchar(128)
		SET @constraintName = (
		SELECT TOP 1 CONSTRAINT_NAME
			FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
			WHERE TABLE_NAME = @tableName AND COLUMN_NAME = @columnName)
		IF @constraintName IS NULL BREAK
		EXEC ('ALTER TABLE "'+@tableName+'" DROP CONSTRAINT "'+@constraintName+'"')
	END
SEPARATOR

/****** Dropping column, and ALL of its constraints, too ******/
CREATE PROCEDURE [dbo].[DropColumnWithConstraints]
	@tableName varchar(128),
	@columnName varchar(128)
	AS SET NOCOUNT ON
	SET xact_abort ON

	IF COL_LENGTH(@tableName, @columnName) IS NOT NULL
	BEGIN
		EXEC DropConstraintsForColumn @tableName, @columnName;
		BEGIN
			EXEC ('ALTER TABLE "'+@tableName+'" DROP COLUMN "'+@columnName+'"')
		END
	END
SEPARATOR

/****** Because sometimes, altering a column means we have to drop its constraints, too ******/
/****** Usage e.g. EXEC AlterColumnDroppingItsConstraints 'DT415', 'AUDITED', 'NUMERIC(5, 0) NOT NULL' ******/
CREATE PROCEDURE [dbo].[AlterColumnDroppingItsConstraints]
	@tableName varchar(128),
	@columnName varchar(128),
	@newcolumndefinition varchar(512)
	AS SET NOCOUNT ON
	SET xact_abort ON

	IF COL_LENGTH(@tableName, @columnName) IS NOT NULL
	BEGIN
		EXEC DropConstraintsForColumn @tableName, @columnName;
		BEGIN
			EXEC ('ALTER TABLE "'+@tableName+'" ALTER COLUMN "'+@columnName+'" '+@newcolumndefinition)
		END
	END
SEPARATOR
