SQL批量修改表名

SELECT NAME FROM SYS . ALL_OBJECTS WHERE TYPE = 'U' ORDER BY MODIFY_DATE DESC --查询所有表名

SELECT NAME FROM SYS . ALL_OBJECTS WHERE TYPE = 'U'

AND name LIKE   '%\_0%' escape '\' AND name NOT LIKE '%_OLD'

ORDER BY MODIFY_DATE DESC

 

 

DECLARE @COUNT INT

DECLARE @NAME NVARCHAR ( 100 )

DECLARE @RENAME NVARCHAR ( 100 )

SELECT @COUNT = COUNT ( 1 ) FROM SYS . ALL_OBJECTS WHERE TYPE = 'U' AND name LIKE '%_FOREX_%' --查询循环次数

WHILE @COUNT > 0

BEGIN

       SELECT @NAME = name FROM SYS . ALL_OBJECTS WHERE TYPE = 'U' AND name LIKE '%_FOREX_%' --查询旧表名

       SELECT @RENAME = REPLACE ( name , '_FOREX_' , '_WH_' ) FROM SYS . ALL_OBJECTS WHERE TYPE = 'U' AND name LIKE '%_FOREX_%'   --查询新表名

       EXEC sp_rename @NAME , @RENAME ; --修改表名

       SET @COUNT = @COUNT - 1 ;

END

  

转载于:https://www.cnblogs.com/panz/p/5543329.html