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 |