Renaming table name in SQLServer

Question

I have a database say test in SQLServer 2008 which have almost more than 100 tables inside it . I have requirement that I have to add prefix to these tables .

for e.g. table employee should be renamed as companyName_employee . companyName_ is the prefix I want to apply.So table department should be renamed to companyName_department

Currently I can rename tables one by one by running below query :

sp_rename employee, companyName_employee

sp_rename only renames one table at a time.

I am new to SQLServer so please suggest if there is a way to rename multiple tables in one query

Do you need to preserve the data? What performance requirements are there? — usr
– usr, Commented Oct 1, 2014 at 10:47

Donal · Accepted Answer · 2015-07-15 16:10:03Z

5

You can generate the sp_rename statements using dynamic sql. Like this:

SELECT 'exec sp_rename ''' + name + ''', ''companyName_' + name + ''''
FROM sysObjects
WHERE type = 'U'

edited Jul 15, 2015 at 16:10

answered Oct 1, 2014 at 10:52

Donal

33.2k10 gold badges69 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Shirish Bari Over a year ago

and how to run this dynamic SQL ... because When I run this query it is giving result as exec sp_rename 'qualityrating', ' companyName_qualityrating' but It does actually renames the tables . how to run this statement ?

Pred Over a year ago

Copy all the statements, then execute them by hand. This query only generates the queries, it will not execute them.

Donal Over a year ago

@Shree It is possible to generate the update statements and execute them at once - but this would require cursors and is a lot more involved. Probably not worth it for a once off table rename like this. As Pred said - with this answer you can generate the statements, copy and run them manually.

Collectives™ on Stack Overflow

Renaming table name in SQLServer

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related