February 26, 2008 at 5:06 am
I want to rename one of the schemas in my database (SQL Server 2005). I want all the associated tables to move to the new schema name. Does anyone know how this can be done?
I have been searching for a while, and I am beginning to believe that it is not possible.
February 26, 2008 at 7:56 am
You can't rename the schema as far as I know, but if you have 2 schemas you can move objects from one to the other.
Assuming two schemas testing and testing2
CREATE TABLE testing.bob (a INT)
ALTER SCHEMA testing2 transfer testing.bob
INSERT INTO testing2.bob VALUES(1)
SELECT * FROM testing2.bob
February 26, 2008 at 7:58 am
However, this will NOT change references in stored procedures, triggers and functions that reference the old schema.
Understand this was by design. The whole idea behind schemas was to eliminate the need to do this. When they were users, that was understood. But you create schemas so that you can assign users to and from them.
February 27, 2008 at 9:48 am
Yes as mentioned aboe you can't rename the schema. create a new schema and move the objects to the new schema. Also take measures to seee if anything is impacted with this change.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply