扩展存储过程sp_rename的使用方法:
sp_rename
[
@objname =
]
'
object_name
'
,
[
@newname =
]
'
new_name
'
[
, [ @objtype =
]
'
object_type
'
]
USE
[
DATABASENAME
]
GO
DECLARE @i int
DECLARE @count int
DECLARE @oldname nvarchar ( 200 )
DECLARE @newname nvarchar ( 200 )
DECLARE @tbl TABLE (ID INT IDENTITY ( 1 , 1 ),TABLENAME NVARCHAR ( 200 ))
INSERT INTO @tbl
SELECT NAME FROM SYS.OBJECTS WHERE NAME LIKE ' %_TAB ' AND TYPE = ' U '
SELECT @count = COUNT ( * ) FROM @tbl
SET @i = 1
WHILE ( @i <= @count )
BEGIN
SET @oldname = ''
SET @newname = ''
SELECT @oldname = TABLENAME , @newname = TABLENAME + ' _V2.0 ' FROM @tbl WHERE ID = @i
EXEC sp_rename @oldname , @newname
SET @i = @i + 1
END
GO
GO
DECLARE @i int
DECLARE @count int
DECLARE @oldname nvarchar ( 200 )
DECLARE @newname nvarchar ( 200 )
DECLARE @tbl TABLE (ID INT IDENTITY ( 1 , 1 ),TABLENAME NVARCHAR ( 200 ))
INSERT INTO @tbl
SELECT NAME FROM SYS.OBJECTS WHERE NAME LIKE ' %_TAB ' AND TYPE = ' U '
SELECT @count = COUNT ( * ) FROM @tbl
SET @i = 1
WHILE ( @i <= @count )
BEGIN
SET @oldname = ''
SET @newname = ''
SELECT @oldname = TABLENAME , @newname = TABLENAME + ' _V2.0 ' FROM @tbl WHERE ID = @i
EXEC sp_rename @oldname , @newname
SET @i = @i + 1
END
GO
关于sp_rename的详细用法请参考联机丛书:
sp_rename (Transact-SQL)
http://technet.microsoft.com/zh-cn/library/ms188351.aspx