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

72 阅读 0 评论 48 点赞

我是靠谱客的博主炙热汉堡，最近开发中收集的这篇文章主要介绍SQL批量重命名中文字段名为对应拼音首字母字段名脚本，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

操作步骤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