-- = Renaming multiple tables within a database = --

DECLARE @currentTableName SYSNAME
DECLARE @newTableName SYSNAME
DECLARE @NameToBeAppended VARCHAR(100)
DECLARE @currentID	INT

SET @NameToBeAppended = 'zzz'

-- Creating a table which will hold all the table names within a database
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[DBO].[tblListOfTableName]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) 
	DROP TABLE [DBO].[tblListOfTableName]


SELECT	DISTINCT 
		IDENTITY(int, 1,1) AS TableID,
		TABLE_NAME AS TableNames
INTO	tblListOfTableName
FROM	INFORMATION_SCHEMA.COLUMNS
WHERE	TABLE_NAME <> 'tblListOfTableName'


WHILE EXISTS (SELECT * FROM tblListOfTableName)
	BEGIN
		-- Pointing to the first table from the list
		SELECT	@currentID = (SELECT MIN(TableID) FROM tblListOfTableName)

		SELECT	@currentTableName = TableNames
		FROM	tblListOfTableName
		WHERE	TableID = @currentID
		 
		SELECT @newTableName = @NameToBeAppended + @currentTableName
		PRINT 'Old Table Name: ' + @currentTableName
		PRINT 'New Table Name: ' + @newTableName
		
		-- Renaming table
		EXEC SP_RENAME @currentTableName, @newTableName
		
		-- Deleting the table name out of the list to deal with the next one until the table is empty
		DELETE
		FROM	tblListOfTableName
		WHERE	TableID = @currentID
	END