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'囕'
中文字符unicode查询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 lumns 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 lumns 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 lumns 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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。