Rename Table Name

Transact-SQL
1

Hi there,

I was wondering if it is possible to rename tables based on criteria? I need to remove some characters at the end of the names of some tables. For example, one table is called 01_calls_children_turned_away_2$ & one is called 01_gender_201503$

I would like them to be called 01_calls_children_turned_away & 01_gender respectively.

Thank you!

2

You could write a query to automatically rename them, but a safer approach would be to generate the query for renaming, and then run that query manually. For example, if you run the following query, it generates a set of statements.

SELECT 'EXEC sp_rename ''' + name + '''  ''' + REPLACE(name,'$','')  + '''' FROM sys.tables WHERE name LIKE '%$'

You can examine the results and see if that is exactly what you want to do. If it is, then copy that to a query window and run it. The advantage of doing it this way is that you know exactly what you are going to do BEFORE you do it.

The REPLACE function that I used in the example may not be exactly what you want to use if you have other '$' symbols in your table name that you want to retain.

3

Hi James,

Thanks for your response. I altered your query a little bit to this:

SELECT 'EXEC sp_rename ' +Left(TABLE_NAME, 30)+ '' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%_2$%'

However, I am confused on what you mean to "copy that to a query window and run it." When I highlight and run it I get several rows of records like:

EXEC sp_rename '11_Shelter_discharges_reasons
EXEC sp_rename '18_calls_children_turned_away
etc

I'm confused how this select statement permanently changes the table names.

4

Here is an example that you can copy and run. Be sure to run it in a test environment where you can afford to lose data/tables.

USE TestDatabase
GO

-- Create 3 test tables. Two have names ending in $.
CREATE TABLE dbo.Test1$( id INT );
GO

CREATE TABLE dbo.Test2$( id INT );
GO

CREATE TABLE dbo.TestB (id INT );
GO

-- Generate the script to rename the tables whose name end with $.
SELECT 'EXEC sp_rename ''' + name + ''',  ''' + REPLACE(name,'$','')  + '''' FROM sys.tables WHERE name LIKE '%$'

-- Copy the results of the above select statement to a query window and run it to rename the tables.
EXEC sp_rename 'Test1$',  'Test1'
EXEC sp_rename 'Test2$',  'Test2'
5

Thank you so much James

Now I see how it works. I haven't worked to much with dynamic sql.

One additional questions. Would it be possible to include this into a Stored Procedure so the process can be automated or do you have to always take the results and copy to a query window and run (manual)?

6

You could conceivably write something to execute those statements as part of the stored procedure. However, it is not a sound practice. Renaming tables should be something that is done very rarely, if at all. Do you regularly get new tables with the $ character in the names? If so, it might be better to address that at the point where the tables are created in the first place.

7

Hi James,

Thanks again. Unfortunately, the process is a little, let's say political, so I have to work with what I have. Yes they provide us with hundreds of .csv files every month. I found VBA code that puts the .csv files into one .xlsx file under multiple tabs and then I upload the .xlsx file into the SQL db. The naming in some of the tabs gets cut off b/c the limit is 31 chars.

When I import the .xlsx file into SQL though I get the $ char at the end and I'm not sure why.