更正:应该是
select 'EXEC sp_rename '''+name+'.[老名]'','+ '''新名'',''COLUMN''' from sysobjects a where type='U'
and exists(select 1 from syscolumns b where b.name='老名' and b.id=a.id )
在SQL SERVER中怎么批量修改字段名
在SQL SERVER 数据库中,有好多300多个表,想将多个表中字段名为“Number”的
字段名重命名,怎么作? 问题点数:0、回复次数:4Top
1 楼yone(yone)回复于 2003-10-26 20:11:48 得分 0
select ' EXEC sp_rename ' + name+'.[Number],'+ '''newname'',''COLUMN''' from sysobjects where type ='U'
执行这段SQL,取返回记录,再在QUERY ANALYZER中执行Top
2 楼yone(yone)回复于 2003-10-26 20:16:33 得分 0
补充以下在加一个条件
select ' EXEC sp_rename ' + name+'.[Number],'+ '''newname'',''COLUMN''' from sysobjects a where type ='U'
and exist(select 1 from syscolumns b where b.name ='Number' and b.id = a.id )
执行这段SQL,取返回记录,再在QUERY ANALYZER中执行
Top
3 楼pengdali()回复于 2003-10-26 21:25:13 得分 0
declare cursor_insert cursor for select d.name from syscolumns a, sysobjects d where a.id=d.id and d.xtype='U' and a.name=@旧列名
declare @i varchar(500)
open cursor_insert
fetch cursor_insert into @i
while @@fetch_status=0
begin
set @i=@i+'.'+@旧列名
exec sp_rename @i,@新列名
fetch cursor_insert into @i
end
close cursor_insert
deallocate cursor_insertTop
4 楼pengdali()回复于 2003-10-26 21:25:40 得分 0
举例:
declare @旧列名 varchar(500),@新列名 varchar(500)
select @旧列名='Number',@新列名='新列名'
declare cursor_insert cursor for select d.name from syscolumns a, sysobjects d where a.id=d.id and d.xtype='U' and a.name=@旧列名
declare @i varchar(500)
open cursor_insert
fetch cursor_insert into @i
while @@fetch_status=0
begin
set @i=@i+'.'+@旧列名
exec sp_rename @i,@新列名
fetch cursor_insert into @i
end
close cursor_insert
deallocate cursor_insert
--------------------------------------------
更正:应该是
select 'EXEC sp_rename '''+name+'.[老名]'','+ '''新名'',''COLUMN''' from sysobjects a where type='U'
and exists(select 1 from syscolumns b where b.name='老名' and b.id=a.id )
---------------------------------------------
sqlserver 如何批量更改表名和字段名前缀? | ||||||
---|---|---|---|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|