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