How to Change Multiple Data Table Names in SQL Server

2015-10-06

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