sp_rename_tables, Sql Server

Language: Layout:

IF OBJECT_ID('dbo.FactAccountBalance') IS NOT NULL
    DROP TABLE dbo.FactAccountBalance;
IF OBJECT_ID('dbo.FactAccount') IS NOT NULL
    DROP TABLE dbo.FactAccount;
IF OBJECT_ID('dbo.DimAccountTime') IS NOT NULL
    DROP TABLE dbo.DimAccountTime;
IF OBJECT_ID('dbo.FactItems') IS NOT NULL
    DROP TABLE dbo.FactItems;
IF OBJECT_ID('dbo.FactItemsBalance') IS NOT NULL
    DROP TABLE dbo.FactItemsBalance;
IF OBJECT_ID('dbo.DimItemsTime') IS NOT NULL
    DROP TABLE dbo.DimItemsTime;

CREATE TABLE FactAccount(account_id int primary key, account_item int);
CREATE TABLE FactAccountBalance(account_id int references FactAccount(account_id), balance decimal);
CREATE TABLE DimAccountTime(account_id int, account_time datetime);

--= returns tables
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE 
FROM   INFORMATION_SCHEMA.TABLES 
WHERE  TABLE_NAME LIKE '%account%'
AND    TABLE_TYPE = 'BASE TABLE'
AND    TABLE_CATALOG = 'rextester';
GO

--= cursor to rename table's name
-------------------------------------------------------------
CREATE PROCEDURE dbo.renameTables(@Catalog sysname, @OldName sysname, @NewName sysname)
AS
BEGIN

DECLARE @schema nvarchar(128);
    DECLARE @table sysname;
    DECLARE @cmd nvarchar(max);
                                  
    DECLARE curRes CURSOR FAST_FORWARD FOR 
    SELECT TABLE_SCHEMA, TABLE_NAME 
    FROM   INFORMATION_SCHEMA.TABLES 
    WHERE  TABLE_CATALOG = @Catalog
    AND    TABLE_NAME LIKE '%` + @OldName + `%'
    AND    TABLE_TYPE = 'BASE TABLE';

OPEN curRes;
    FETCH NEXT FROM curRes INTO @schema, @table;
    
    WHILE @@FETCH_STATUS = 0  
    BEGIN

set @cmd =   'sp_rename ''' + @schema + '.' + @table + ''', '''
                     + @schema + '.' + replace(@table, @OldName, @NewName) + '''';
        
        --EXEC(@cmd);
        select @cmd;
        IF @@ERROR <> 0
        BEGIN
            CLOSE curRes;
            DEALLOCATE curRes;
            RAISERROR('Error renaming tables',16,1);
            RETURN -1;
        END
        
        FETCH NEXT FROM curRes INTO @schema, @table;
    END
    
    CLOSE curRes;
    DEALLOCATE curRes;
    RETURN 0;
END
GO
--=-------------------------------------------------------------
                      
exec renameTables 'rextester', 'account', 'items';
go

--= returns tables
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE 
FROM   INFORMATION_SCHEMA.TABLES 
WHERE  TABLE_NAME LIKE '%items%'
AND    TABLE_TYPE = 'BASE TABLE'
AND    TABLE_CATALOG = 'rextester';

View schema
Execution time: 0,1 sec, rows selected: 4, rows affected: 0, absolute service time: 0,25 sec