批量重命名中文字段名为对应拼音首字母字段名脚本代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】批量重命名中文字段名为对应拼音首字母字段名脚本

作者:xqf222 / 发布于2014/2/25/ 985

批量重命名中文字段名为对应拼音首字母字段名脚本

操作步骤1:生成获取中文字符串对应的拼音首字母字符串函数

操作步骤2:执行重命名中文字段名为拼音字首母字段名脚本

--1、SQL获取中文字符串对应的拼音首字母字符串函数
Create function fun_getPY
(
 @str nvarchar(4000)
)
returns nvarchar(4000)
as
begin
 declare @word nchar(1),@PY nvarchar(4000)

 set @PY=''

 while len(@str)>0
 begin
 set @word=left(@str,1)

 --如果非汉字字符，返回原字符
 set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901
 then (
 select top 1 PY
 from
 (
 select 'A' as PY,N'驁' as word
 union all select 'B',N'簿'
 union all select 'C',N'錯'
 union all select 'D',N'鵽'
 union all select 'E',N'樲'
 union all select 'F',N'鰒'
 union all select 'G',N'腂'
 union all select 'H',N'夻'
 union all select 'J',N'攈'
 union all select 'K',N'穒'
 union all select 'L',N'鱳'
 union all select 'M',N'旀'
 union all select 'N',N'桛'
 union all select 'O',N'漚'
 union all select 'P',N'曝'
 union all select 'Q',N'囕'
 union all select 'R',N'鶸'
 union all select 'S',N'蜶'
 union all select 'T',N'籜'
 union all select 'W',N'鶩'
 union all select 'X',N'鑂'
 union all select 'Y',N'韻'
 union all select 'Z',N'咗'
 ) T
 where word>=@word collate Chinese_PRC_CS_AS_KS_WS
 order by PY ASC
 )
 else @word
 end)
 set @str=right(@str,len(@str)-1)
 end

 return @PY

end





--2、SQL批量重命名中文字段名为对应拼音首字母字段名脚本



--查询当前数据库中的所有中文字段名列表
select b.name as tablename,a.name as columnname from sys.columns a left join sys.objects b on a.object_id=b.object_id where UNICODE(a.name) BETWEEN 19968 AND 19968+20901 order by b.name

--定义游标查询变量
declare @TableName nvarchar(250)
declare @ColumnName nvarchar(250)

--声明读取数据库所有数据表名称游标mycursor1
declare mycursor1 cursor for select b.name as tablename,a.name as columnname from sys.columns a left join sys.objects b on a.object_id=b.object_id where UNICODE(a.name) BETWEEN 19968 AND 19968+20901 order by b.name

--打开游标
open mycursor1
--从游标里取出数据赋值到我们刚才声明的数据表名变量中
fetch next from mycursor1 into @TableName,@ColumnName
--如果游标执行成功
while (@@fetch_status=0)
begin

--定义重命名字段变量
declare @NewColumnName varchar (250)
select @NewColumnName=dbo.fun_getPY(@ColumnName)

declare @renameCommand nvarchar(2000)
set @renameCommand=@TableName+'.'+@ColumnName

--指定重命名字段指令
exec sp_rename @renameCommand,@NewColumnName,'column'

--用游标去取下一条记录
 fetch next from mycursor1 into @TableName,@ColumnName
end

--关闭游标
close mycursor1
--撤销游标
deallocate mycursor1

--查询重命名后的当前数据库中的所有中文字段名列表
select b.name as tablename,a.name as columnname from sys.columns a left join sys.objects b on a.object_id=b.object_id where UNICODE(a.name) BETWEEN 19968 AND 19968+20901 order by b.name

试试其它关键字

　量重命名中文字段名　

同语言下

可能有用的

xqf222贡献的其它代码(82)