Renaming Schema in SQL Server 2008


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.
Who is Participating?
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
There is no facility to rename an existing schema. You have  to create a new schema 'employee' and transfer the ownership.
Chris LuttrellSenior Database ArchitectCommented:
Red Gate has a refactor tool. I have not tried a schema yet, but you may want to check it out.
r_pat72Author Commented:
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.

Chris LuttrellConnect With a Mentor Senior Database ArchitectCommented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.