IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'p_AlterColumn') AND type in (N'P', N'PC'))
DROP PROCEDURE p_AlterColumn
GO
CREATE PROCEDURE p_AlterColumn
    @TableName        VARCHAR(50),
    @ColumnName       VARCHAR(50),
    @ColumnType       VARCHAR(50),  
    @ColumnIsNull     VARCHAR(10),
    @ModifyFlag       VARCHAR(10),  
    @DefaultValue VARCHAR(255) = NULL
AS
    SET NOCOUNT ON
	DECLARE @DefaultName VARCHAR(255)
	DECLARE @DefaultValueSQL VARCHAR(2000)
		DECLARE @IndexName     SYSNAME
		DECLARE @FieldName     SYSNAME
		DECLARE @PrevIndexName SYSNAME
		DECLARE @ExistColumn	 INT
		DECLARE @IsPrimaryKey  INT
		DECLARE @ColumnID      SMALLINT
		DECLARE @Clustered     VARCHAR(15)
		DECLARE @DropSQL       VARCHAR(8000)
		DECLARE @CreateSQL     VARCHAR(8000)

	IF @ColumnType = 'BLOB'
	BEGIN
		SET @ColumnType = 'IMAGE'
	END
	ELSE IF @ColumnType = 'NCLOB'
	BEGIN
		SET @ColumnType = 'NTEXT'
	END
	ELSE IF @ColumnType = 'XMLTYPE'
	BEGIN
		SET @ColumnType = 'XML'
	END

	SET @DefaultValueSQL = ''
	IF SUBSTRING(@ModifyFlag,4,1) = '1' or SUBSTRING(@ModifyFlag,1,1) = '1'　OR (SUBSTRING(@ModifyFlag,3,1) = '1' AND @DefaultValue <> 'NULL' AND @DefaultValue <> '')	
	BEGIN
		SELECT @DefaultName = ISNULL(object_name(o.constid), '')
		  FROM sysconstraints O
			   INNER JOIN syscolumns C ON O.ID = C.id AND O.colid = C.colid
		 WHERE O.ID = OBJECT_ID(@TableName) AND C.name = @ColumnName
		IF LEN(@DefaultName) > 0
		BEGIN
			EXECUTE('ALTER TABLE ' + @TableName + ' DROP CONSTRAINT ' + @DefaultName)
		END
		ELSE
		BEGIN  
			SELECT @DefaultName = 'DF_' + @TableName + '_' + @ColumnName
		END

		IF @DefaultValue IS NOT NULL AND @DefaultValue <> 'NULL' AND @DefaultValue <> ''	
		BEGIN
			IF LEFT(@DefaultValue, 3) = '{ts'
			BEGIN
				SET @DefaultValue = REPLACE(REPLACE(@DefaultValue, '{ts', ''), '}', '')
				
			END
			SET @DefaultValueSQL ='ALTER TABLE ' + @TableName + ' ADD  CONSTRAINT ' + @DefaultName + ' DEFAULT ('
								   + @DefaultValue + ') FOR ' + @ColumnName
		END
	END		

	IF (SUBSTRING(@ModifyFlag,3,1) = '1' AND UPPER(@ColumnIsNull) = 'NOT NULL') or (SUBSTRING(@ModifyFlag,1,1) = '1') or (SUBSTRING(@ModifyFlag,2,1) = '1')
	
	BEGIN
		SELECT @DropSQL = '', @PrevIndexName = '', @CreateSQL = '', @IsPrimaryKey = 0

		
		SELECT @ColumnID = colid
		  FROM syscolumns
		 WHERE name = @ColumnName AND id = OBJECT_ID(@TableName)

		
		DECLARE curIndexes CURSOR FOR
						   SELECT i.name AS IndexName, c.name AS FieldName, CASE WHEN o.id IS NULL THEN 0
																				 ELSE OBJECTPROPERTY(o.id, 'IsPrimaryKey')
																			END AS IsPrimaryKey,
								  CASE i.indid WHEN 1 THEN ' CLUSTERED '
											   ELSE ' NONCLUSTERED '
								  END AS IsClustered
							 FROM sysindexes i
								  INNER JOIN sysindexkeys k ON i.id = k.id AND i.indid = k.indid
								  INNER JOIN syscolumns c ON i.id = c.id AND c.colid = k.colid
								  LEFT JOIN sysobjects o ON o.id = OBJECT_ID(i.name)
							WHERE i.id = OBJECT_ID(@TableName) AND INDEXPROPERTY(i.id, i.name, 'IsAutoStatistics') <> 1
							  AND i.indid IN (SELECT indid
												FROM sysindexkeys
											   WHERE id = k.id AND colid = @ColumnID)
						 ORDER BY i.indid
		OPEN curIndexes

		
		FETCH NEXT FROM curIndexes INTO @IndexName, @FieldName, @IsPrimaryKey, @Clustered
		WHILE(@@FETCH_STATUS = 0)
		BEGIN
			IF @PrevIndexName <> @IndexName
			BEGIN
				IF @IsPrimaryKey = 0
				BEGIN
					SELECT @DropSQL = @DropSQL + 'DROP INDEX ' + @TableName + '.' + @IndexName + CHAR(10)
					IF @CreateSQL = ''
						SELECT @CreateSQL = 'CREATE' + @Clustered + 'INDEX ' + @IndexName + ' ON ' + @TableName + ' (' + @FieldName
					ELSE
						SELECT @CreateSQL = @CreateSQL + ')' + CHAR(10) + 'CREATE' + @Clustered + 'INDEX ' + @IndexName + ' ON ' + @TableName + ' (' + @FieldName
				END
				ELSE
				BEGIN
					SELECT @DropSQL = @DropSQL + 'ALTER TABLE ' + @TableName + ' DROP CONSTRAINT ' + @IndexName + CHAR(10)
					IF @CreateSQL = ''
						SELECT @CreateSQL = 'ALTER TABLE ' + @TableName + ' ADD CONSTRAINT ' + @IndexName + ' PRIMARY KEY' + @Clustered + '(' + @FieldName
					ELSE
						SELECT @CreateSQL = @CreateSQL + ')' + CHAR(10) + 'ALTER TABLE ' + @TableName + ' ADD CONSTRAINT ' + @IndexName + ' PRIMARY KEY' + @Clustered + '(' + @FieldName
				END
				SELECT @PrevIndexName = @IndexName
			END
			ELSE
			BEGIN
				SELECT @CreateSQL = @CreateSQL + ',' + @FieldName
			END
			FETCH NEXT FROM curIndexes INTO @IndexName, @FieldName, @IsPrimaryKey, @Clustered
		END

		CLOSE curIndexes
		DEALLOCATE curIndexes

		IF @CreateSQL <> ''
			SELECT @CreateSQL = @CreateSQL + ')'

		
		EXECUTE (@DropSQL)
	END

	IF @ColumnType = '' OR @ColumnType = 'NULL'
	BEGIN
		SELECT @ExistColumn = count(1) FROM syscolumns WHERE ID = OBJECT_ID(@TableName) AND name = @ColumnName
		IF @ExistColumn = 1
		BEGIN
			EXECUTE ('ALTER TABLE ' + @TableName + ' DROP COLUMN ' + @ColumnName + ' ')
		END
	END
	ELSE
	BEGIN
        IF (SUBSTRING(@ModifyFlag,3,1) = '1' AND @ColumnIsNull = 'NOT NULL' AND @DefaultValue <> 'NULL' AND @DefaultValue <> '')	
        BEGIN
            IF SUBSTRING(@ModifyFlag,1,1) = '1' 
            BEGIN
        	    
                EXECUTE ('ALTER TABLE ' + @TableName + ' ALTER COLUMN ' + @ColumnName + ' ' + @ColumnType + ' ')
            END
            EXECUTE('UPDATE ' + @TableName + ' SET ' + @ColumnName + ' = ' + @DefaultValue + ' WHERE ' + @ColumnName + ' IS NULL ' )
        END
		IF(SUBSTRING(@ModifyFlag,1,1) = '1' OR SUBSTRING(@ModifyFlag,2,1) = '1' OR SUBSTRING(@ModifyFlag,3,1) = '1')	
		BEGIN
			
			EXECUTE ('ALTER TABLE ' + @TableName + ' ALTER COLUMN ' + @ColumnName + ' ' + @ColumnType + ' ' + @ColumnIsNull + ' ')
		END
		IF @DefaultValueSQL <> ''
		BEGIN
			EXECUTE (@DefaultValueSQL)
		END
		IF @CreateSQL <> ''
		BEGIN
			EXECUTE (@CreateSQL)
		END
	END
GO

