CODEWITHSUNDEEP
sqldatabasesql-server-2008-r2
Shirish Bari
Shirish Bari

Reputation: 2722

Renaming table name in SQLServer

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

Upvotes: 2

Views: 172

Answers (1)

Donal
Donal

Reputation: 32823

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

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

Upvotes: 5

Related Questions