/********************************************************
功能实现:为列重新命名
参数说明:
1.@TableName: 表名称
2.@Old_ColName: 旧列名
3.@New_ColName: 新列名
使用说明:
EXEC RenameColumnName 'TABLENAME','OLD_COLNAME','NEW_COLNAME'
*********************************************************/
CREATE PROC RenameColumnName
@TableName SYSNAME,
@Old_ColName SYSNAME,
@New_ColName SYSNAME
AS
BEGIN
/************************************
----判断表的集中输入形式:
1.schema.TableName
2.TableName
3.[TableName]
4.[schema].[TableName]
************************************/
SET NOCOUNT ON;
DECLARE @Schema_Name SYSNAME=SCHEMA_NAME();--------当前默认的架构名
DECLARE @SimpleTableName SYSNAME;
DECLARE @Index AS INT=0;
DECLARE @ColName SYSNAME;
DECLARE @AllTableName SYSNAME;
SET @TableName=REPLACE(REPLACE(@TableName,'[',''),']','');
SET @Index=CHARINDEX('.',@TableName);
IF @Index>0
BEGIN
SET @Schema_Name=LEFT(@TableName,@Index-1);
SET @SimpleTableName=RIGHT(@TableName,LEN(@TableName)-@Index);
END
ELSE
BEGIN
SET @SimpleTableName=@TableName;
END
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=@Schema_Name AND TABLE_NAME=@SimpleTableName)
BEGIN
SET @ColName=@Old_ColName;
IF EXISTS
(
SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA=@Schema_Name AND TABLE_NAME=@SimpleTableName AND COLUMN_NAME=@ColName
)
BEGIN
SET @AllTableName=@TableName+'.'+@Old_ColName;
EXEC SP_RENAME @AllTableName,@New_ColName
PRINT '修改成功!!!'
END
ELSE
BEGIN
PRINT '要重命名的列不存在'
END
END
ELSE
BEGIN
PRINT '当前表不存在';
END
SET NOCOUNT OFF;
END