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
