SQL Server – Stored Procedure to Rename Table and Column Names

sql server

There is an example dataBase in which we have some tables.

The word "Account" is included in the names of 5 tables in that database.

For example we have:

Table1: FactAccount     
Table2: FactAccountBalance
Table3: DimAccountTime

"Account" is also included in the name of some of the columns.

We need a stored procedure to replace the word "Account" with "Items" in both column and table names, so that at the end of the stored procedure we have: 

Table1: FactItems
Table2: FactItemsBalance
Table3: DimItemsTime

I have tried a combination of sp_rename and the replace function but I was not successful.

Best Answer

This solution uses the sys.sp_rename() database engine stored procedure.

Changes the name of a user-created object in the current database. This object can be a table, index, column, alias data type, or Microsoft .NET Framework common language runtime (CLR) user-defined type.

But read carefully the warning message:

Caution: Changing any part of an object name can break scripts and stored procedures. We recommend you do not use this statement to rename stored procedures, triggers, user-defined functions, or views; instead, drop the object and re-create it with the new name.

You can get table and column names by querying the INFORMATION_SCHEMA system information schema views (but see The case against INFORMATION_SCHEMA views by Aaron Bertrand).

--= 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'

| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME         | TABLE_TYPE |
|---------------|--------------|--------------------|------------|
| rextester     | dbo          | DimAccountTime     | BASE TABLE |
| rextester     | dbo          | FactAccount        | BASE TABLE |
| rextester     | dbo          | FactAccountBalance | BASE TABLE |

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

| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME         | COLUMN_NAME  |
|---------------|--------------|--------------------|--------------|
| rextester     | dbo          | FactAccount        | account_id   |
| rextester     | dbo          | FactAccount        | account_item |
| rextester     | dbo          | FactAccountBalance | account_id   |
| rextester     | dbo          | DimAccountTime     | account_id   |
| rextester     | dbo          | DimAccountTime     | account_time |

I have set up the next schema on Rextester:

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
);

Rename columns

Syntax: sp_rename 'schema.table.column_name', 'new_column_name', 'COLUMN'

--= cursor to rename columns
-------------------------------------------------------------
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

Rextester here

Rename tables

Syntax: sp_rename 'schema.table_name', 'schema.new_table_name'

--= 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

Rextester here

Now, if you query INFORMATION_SCHEMA again, replacing account with items, this is the result:

| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME           | TABLE_TYPE |
|---------------|--------------|----------------------|------------|
| rextester     | dbo          | dbo.DimItemsTime     | BASE TABLE |
| rextester     | dbo          | dbo.FactItems        | BASE TABLE |
| rextester     | dbo          | dbo.FactItemsBalance | BASE TABLE |

| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME           | COLUMN_NAME |
|---------------|--------------|----------------------|-------------|
| rextester     | dbo          | dbo.FactItems        | Items_id    |
| rextester     | dbo          | dbo.FactItems        | Items_item  |
| rextester     | dbo          | dbo.FactItemsBalance | Items_id    |
| rextester     | dbo          | dbo.DimItemsTime     | Items_id    |
| rextester     | dbo          | dbo.DimItemsTime     | Items_time  |
Related Question