sp_rename_columns, 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 columns
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME 
FROM   INFORMATION_SCHEMA.COLUMNS 
WHERE  COLUMN_NAME LIKE '%account%'
AND    TABLE_CATALOG = 'rextester';
GO

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

DECLARE @schema nvarchar(128);
    DECLARE @table sysname;
    DECLARE @column sysname;
    DECLARE @cmd nvarchar(max);
                                  
    DECLARE curRes CURSOR FAST_FORWARD FOR 
    SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME 
    FROM   INFORMATION_SCHEMA.COLUMNS 
    WHERE  COLUMN_NAME LIKE '%' + @OldName + '%'
    AND    TABLE_CATALOG = @Catalog
    
    OPEN curRes;
    FETCH NEXT FROM curRes INTO @schema, @table, @column;
    
    WHILE @@FETCH_STATUS = 0  
    BEGIN

set @cmd =   'sp_rename ''' + @table + '.' + @column + ''', '''
                 + replace(@column, 'Account', 'Items') + ''', ''COLUMN''';
        
        EXEC(@cmd);
        
        IF @@ERROR <> 0
        BEGIN
            CLOSE curRes;
            DEALLOCATE curRes;
            RAISERROR('Error renaming columns',16,1);
            RETURN -1;
        END
        
        FETCH NEXT FROM curRes INTO @schema, @table, @column;
    END
    
    CLOSE curRes;
    DEALLOCATE curRes;
    RETURN 0;
END
GO
--=-------------------------------------------------------------

exec renameColumns 'rextester', 'account', 'items';
go

--= returns columns
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME 
FROM   INFORMATION_SCHEMA.COLUMNS 
WHERE  COLUMN_NAME LIKE '%items%'
AND    TABLE_CATALOG = 'rextester';

View schema
Execution time: 0,11 sec, rows selected: 16, rows affected: 0, absolute service time: 0,27 sec