sqlserver修改字段

SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
GO

CREATE PROCEDURE p_AlterColumn
    @TableName VARCHAR(50),
    @ColumnName VARCHAR(50),
    @ColumnType VARCHAR(50), --ModifyFlag='1000' ColumnType = 'NULL' 表示删除
    @ColumnIsNull VARCHAR(10),
    @ModifyFlag VARCHAR(10), --数据类型，精度，为空，缺省值分别占1位。改了缺省值和精度该值为：'0101'
    @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 @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' --修改缺省值
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 --无记录是为null，LEN(NULL)>0为false
        SELECT @DefaultName = 'DF_' + @TableName + '_' + @ColumnName;
    END;
    IF @DefaultValue IS NOT NULL
       AND @DefaultValue <> 'NULL' --isnull表示删除
    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; --SUBSTRING(@ModifyFlag,4,1) = '1'	--修改缺省值
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
    EXECUTE ('ALTER TABLE ' + @TableName + ' DROP COLUMN ' + @ColumnName + ' ');
END;
ELSE
BEGIN
    IF (SUBSTRING(@ModifyFlag, 1, 1) = '1') --修改类型
    BEGIN
        EXECUTE ('ALTER TABLE ' + @TableName + ' ALTER COLUMN ' + @ColumnName + ' ' + @ColumnType + ' ' + @ColumnIsNull + ' ');
        IF (SUBSTRING(@ModifyFlag, 3, 1) = '1' AND @ColumnIsNull = 'NOT NULL') --“是否为空”有null修改为notnull，必须要有缺省值
        BEGIN
            EXECUTE ('UPDATE ' + @TableName + ' SET ' + @ColumnName + ' = ' + @DefaultValue + ' WHERE ' + @ColumnName + ' IS NULL ');
        END;
    END;
    ELSE -- 如果修改了精度、为空
    BEGIN
        IF (SUBSTRING(@ModifyFlag, 3, 1) = '1' AND @ColumnIsNull = 'NOT NULL') --“是否为空”有null修改为notnull，必须要有缺省值
        BEGIN
            EXECUTE ('UPDATE ' + @TableName + ' SET ' + @ColumnName + ' = ' + @DefaultValue + ' WHERE ' + @ColumnName + ' IS NULL ');
        END;
        IF (
               SUBSTRING(@ModifyFlag, 2, 1) = '1'
               OR SUBSTRING(@ModifyFlag, 3, 1) = '1'
           ) --修改了精度和为空才执行，只修改缺省值不执行
        BEGIN
            EXECUTE ('ALTER TABLE ' + @TableName + ' ALTER COLUMN ' + @ColumnName + ' ' + @ColumnType + ' ' + @ColumnIsNull + ' ');
        END;
    END;
    IF @DefaultValueSQL <> ''
    BEGIN
        EXECUTE (@DefaultValueSQL);
    END;
    IF @CreateSQL <> ''
    BEGIN
        EXECUTE (@CreateSQL);
    END;
END;


GO