How to Change Multiple Data Table Names in SQL Server

Published on Tuesday, October 6, 2015

In our one case we have multiple data tables with names includes spaces, we need to cut off all spaces.

We know we can use LTRIM and RTRIM in Transact-SQL, Let us try to use in SQL Server.

Firstly, We need to generate sql scripts to change sql data table names, in **SQL Server, we have to use the procedure sp_rename to change a table name. **

The following is for our case: Generate scripts for trim all table names with ‘RC’ prefix (SysDB is our database name):

SELECT 'EXEC sp_rename '
+ "" + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + "', '
+ "" + LTRIM(RTRIM(TABLE_NAME)) + ""
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='SysDB'
and TABLE_NAME like 'RC%'

Run above script, you will get the result like below, you can get multiple ‘Exec sp_rename’ rows to change single table name one by one. Right click mouse in lower section, Select All and then Copy to a new Query window base current database, then you can run all Exec sp_rename sql script rows, to get your final result (in our case we get all RC table names trimmed)

image