在线求：如何批量修改字段名？

[youzhj]

表名：PQ_HrIa
其中有33个字段，每个字段名都含有字符串“PQ_HrIa”，请问我要一次性把所有字段名里的字母a改成b，怎么用sql语句实现啊？
我在线等。

[子陌红尘]

declare @sql varchar(8000),@name varchar(100)

declare tc cursor for 
select name from syscolumns where id=object_id('PQ_HrIa')

open tc
fetch next from tc into @name

while @@fetch_status=0
begin
    set @sql='exec sp_rename ''PQ_HrIa.'+@name+''' , '''+replace(@name,'a','b')+''',''COLUMN'''
    exec(@sql)
end

close tc
deallocate tc

[youzhj]

我试试去

[youzhj]

我版本是2000的 运行结果是：

服务器: 消息 156，级别 15，状态 1，行 3
在关键字 'cursor' 附近有语法错误。
服务器: 消息 137，级别 15，状态 1，行 9
必须声明变量 '@@status'。

[liangCK]

DECLARE @old_name VARCHAR(256),@new_name VARCHAR(20);

DECLARE cur CURSOR FOR
    SELECT name AS old_name,
           REPLACE(name,'PQ_HrIa','PQ_HrIb') AS new_name
    FROM syscolumns AS c
    WHERE id = OBJECT_ID('PQ_HrIa')
        AND name LIKE '%PQ_HrIa%';

OPEN cur;

FETCH NEXT FROM cur INTO @old_name,@new_name;

WHILE @@FETCH_STATUS=0
  BEGIN
      EXEC('EXEC sp_rename ''PQ_HrIa.' + @old_name + ''',''' + @new_name + ''',''COLUMN''');
      FETCH NEXT FROM cur INTO @name;
  END

CLOSE cur;
DEALLOCATE cur;

[子陌红尘]

[Quote=引用 3 楼 youzhj 的回复:]
我版本是2000的 运行结果是：

SQL code服务器: 消息 156，级别 15，状态 1，行 3
在关键字 'cursor' 附近有语法错误。
服务器: 消息 137，级别 15，状态 1，行 9
必须声明变量 '@@status'。
[/Quote]

declare @sql varchar(8000),@name varchar(100)

declare tc cursor for 
select name from syscolumns where id=object_id('PQ_HrIa')

open tc
fetch next from tc into @name

while @@fetch_status=0
begin
    set @sql='exec sp_rename ''PQ_HrIa.'+@name+''' , '''+replace(@name,'a','b')+''',''COLUMN'''
    exec(@sql)
end

close tc
deallocate tc

[feixianxxx]

[Quote=引用 4 楼 liangCK 的回复:]
SQL codeDECLARE @old_name VARCHAR(256),@new_name VARCHAR(20);

DECLARE cur CURSOR FOR
SELECT name AS old_name,
REPLACE(name,'PQ_HrIa','PQ_HrIb') AS new_name
FROM syscolumns AS c
WHERE id = OBJECT_ID('PQ_HrIa')
AND name LIKE '%PQ_HrIa%';

OPEN cur;

FETCH NEXT FROM cur INTO @old_name,@new_name;

WHILE @@FETCH_STATUS=0
BEGIN
EXEC('EXEC sp_renam…
[/Quote]
UP

[sdhdy]

--5楼的漏了一句。
declare @sql varchar(8000),@name varchar(100)

declare tc cursor for 
select name from syscolumns where id=object_id('PQ_HrIa')

open tc
fetch next from tc into @name

while @@fetch_status=0
begin
    set @sql='exec sp_rename ''PQ_HrIa.'+@name+''' , '''+replace(@name,'a','b')+''',''COLUMN'''
    exec(@sql)
--add*************************
fetch next from tc into @name
end

close tc
deallocate tc

[youzhj]

我补充一下：表PQ_HrIa
字段名：PQ_HrIatotal,PQ_HrIa0,PQ_HrIa1,PQ_HrIa2,......,PQ_HrIa31
想把其中字段名里面的Ia后的a改成b。

[youzhj]

上面的程序运行后都出现问题了
我的事2000版本

[liangCK]

------------------------------------------------
--  Author : liangCK 小梁 
--  Comment: 小梁 爱 兰儿
--  Date   : 2008-10-17 18:58:34
------------------------------------------------
 
--> 生成测试数据: [PQ_HrIa]
IF OBJECT_ID('[PQ_HrIa]') IS NOT NULL DROP TABLE [PQ_HrIa]
CREATE TABLE [PQ_HrIa] (PQ_HrIatotal SQL_VARIANT,PQ_HrIa0 SQL_VARIANT,PQ_HrIa1 SQL_VARIANT,PQ_HrIa2 SQL_VARIANT,PQ_HrIa31 SQL_VARIANT)
 

--SQL查询如下:

DECLARE @old_name VARCHAR(256),@new_name VARCHAR(20);

DECLARE cur CURSOR FOR
    SELECT name AS old_name,
           REPLACE(name,'PQ_HrIa','PQ_HrIb') AS new_name
    FROM syscolumns AS c
    WHERE id = OBJECT_ID('PQ_HrIa')
        AND name LIKE '%PQ_HrIa%';

OPEN cur;

FETCH NEXT FROM cur INTO @old_name,@new_name;

WHILE @@FETCH_STATUS=0
  BEGIN
      EXEC('EXEC sp_rename ''PQ_HrIa.' + @old_name + ''',''' + @new_name + ''',''COLUMN''');
      FETCH NEXT FROM cur INTO @old_name,@new_name;
  END

CLOSE cur;
DEALLOCATE cur;

SELECT * FROM PQ_HrIa

[liangCK]

4楼的..
FETCH NEXT FROM cur INTO @name;
改为
FETCH NEXT FROM cur INTO @old_name,@new_name;

也不会改吗?

[youzhj]

我不知道是不是我自己哪弄错了 半天没有运行成功
我仔细再检查一下

[lalayan520]

顶贴的

[youzhj]

liangCK 小梁 的程序运行成功了，是我自己没有弄对，刚才。
太感谢你了