sql批量修改表名（前缀）--亲测

exec   sp_msforeachtable    
@command1='
  declare   @o   sysname,@n   sysname    
  select   @o=''?''  
  ,@n=stuff(@o,1,charindex(''].[fg_'',@o)+6,''fgjzw_'')  
  ,@n=left(@n,len(@n)-1)  
  exec   sp_rename   @o,@n',  
  @whereand='   and   o.name   like   ''fg_%'''

 

 

 

declare @oldName varchar(30), @newName varchar(30)
declare cursor_taname CURSOR FOR SELECT name,name as newname FROM sysobjects where type='u'
OPEN cursor_taname FETCH NEXT FROM cursor_taname INTO @oldname,@newName WHILE @@FETCH_STATUS=0
BEGIN set @newName=Replace(@newName,'W_','M_') print(@oldname+', mailto:%20+@newName) EXEC sp_rename @oldname,@newName FETCH NEXT FROM cursor_taname INTO @oldname,@newName

END CLOSE cursor_taname DEALLOCATE cursor_taname

 

 declare @oldName varchar(30),
 @newName varchar(30)
declare cursor_taname   CURSOR   FOR  
SELECT name,upper(name) as newname FROM sysobjects where type='u'
OPEN   cursor_taname  
FETCH   NEXT   FROM   cursor_taname   INTO   @oldname,@newName
WHILE   @@FETCH_STATUS=0     
  BEGIN 
SET @newName=(replace(@oldname,'Ad','Fzw_Ad'))
 print(@oldname+',   '+@newName)
EXEC   sp_rename  @oldname,@newName
 FETCH   NEXT   FROM   cursor_taname   INTO   @oldname,@newName
  END     
CLOSE   cursor_taname  
DEALLOCATE   cursor_taname

 

 

警告: 更改对象名的任一部分都可能会破坏脚本和存储过程。

 

刷新数据库表名修改成功！