T-SQL Rename Multiple Tables

Question

Post reply

T-SQL Rename Multiple Tables

forefj

Mr or Mrs. 500

Points: 593
More actions
April 22, 2013 at 8:09 pm

#276712

I am looking to rename a lot of tables using a wildcard,
for example, the table names are:
OrderItem
OrderItems
OrderItemss
I would like them to be renamed to:
OrderItem2
OrderItems2
OrderItemss2
Can this be done using a wildcard so I don't have to specify the full table name? Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Mansfield SSCrazy Points: 2366 More actions · Answer 1

Something like this?

-- Set up test data

declare @tablelist table (

table_name sysname primary key,

new_table_name sysname null

);

insert into @tablelist (table_name)

select 'OrderItem' union all select 'OrderItems' union all select 'OrderItemss'

-- Enumerate new table name

update @tablelist set new_table_name = table_name + '2'

-- Print out rename scripts to run

select 'EXEC dbo.sp_rename @objname = N''' + table_name + ''', @newname = N''' + new_table_name + ''', @objtype = N''OBJECT'''

from @tablelist

forefj Mr or Mrs. 500 Points: 593 More actions · Answer 2

thanks for the code. Not exactly what I was looking to do

I want to able to specify a wildcard like

'O%' to '02%' can I do something like this?

Mansfield SSCrazy Points: 2366 More actions · Answer 3

Can you give a concrete example or two on how the wildcard should work?

forefj Mr or Mrs. 500 Points: 593 More actions · Answer 4

Rename Table '0%' to '02%'

So that all the tables in the database would have a number 2 in the name.

Mansfield SSCrazy Points: 2366 More actions · Answer 5

The SET clause in the UPDATE statement will need to be manually constructed, I'm afraid. e.g.

update @tablelist set new_table_name = '02' + right(table_name, len(table_name) - 2)

where table_name like '0%'

Also consider a CLR based function that will let you specify regular expressions if you find yourself doing advanced string search and replace.

forefj Mr or Mrs. 500 Points: 593 More actions · Answer 6

I am not familiar with using CTE. I usually work with an older version of SQL Server. I ran the SET script, it is showing the error:

Msg 1087, Level 15, State 2, Line 1

Must declare the table variable "@tablelist".

Mansfield SSCrazy Points: 2366 More actions · Answer 7

CLR = Common Language Runtime, i.e. .Net code

CTE = Common Table Expressions

The previous UPDATE statement was not the entire solution, but just an example on how to modify my original script to implement the specific wildcard-equivalent.

The @tablelist is a table variable (I think this feature was introduced in SQL 2005). Unlike a temporary table (the ones with a # or ##), a table variable has the same scope of any other local variable (within the function, stored procedure, or batch that it is declared in).

forefj Mr or Mrs. 500 Points: 593 More actions · Answer 8

I figured out where I was having a problems when I was doing a lookup with sys.tables it was only bring back the second part of the name. So when i executed a sp_rename it would throw an error. the full name is Customer.OrderItems. Thanks for the help