Solved

Renaming Schema in SQL Server 2008

Posted on 2009-06-30
4
2,535 Views
Last Modified: 2012-08-13
Hi,

I wanted to change the schema of my database objects from "emp" to "employee".

How can I do this using scripts. Also "emp" schema is refrerred in storedprocedure and views. Is there any quick way to change to new schema "employee" in those existing storedprocedure and views.

Thanks in advance.
0
Comment
Question by:r_pat72
  • 2
4 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 400 total points
ID: 24750836
There is no facility to rename an existing schema. You have  to create a new schema 'employee' and transfer the ownership.
ALTER SCHEMA emp TRANSFER employee
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24750852
Red Gate has a refactor tool. I have not tried a schema yet, but you may want to check it out. www.red-gate.com
0
 

Author Comment

by:r_pat72
ID: 24854243
ALTER SCHEMA emp TRANSFER employee is for single object. But if i want to update for all objects at a time is there any procedure or method. Please let me know.

0
 
LVL 26

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 100 total points
ID: 24856054
there is not a built in one, especially to change references inside existing Stored Procedures and such.
Good luck, best bet is a 3rd party tool that may search the DDL tables for you.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL server is using more virtual memory. 5 86
SQL Server - Sum Decimal Columns 11 34
Sql Server group by 10 30
PROPERCASE SCRIPT IN SQL 3 7
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

822 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question