Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

Move A Table To A Different Schema

From Wiki

Jump to: navigation, search

You have a table but someone created this table in the wrong schema or maybe you created a new schema and this table belongs in this schema because it make more sense. How can you do this? Let's take a look. Run the code below which will create the TestSchema1 schema and the Test table in that schema


  1. CREATE SCHEMA TestSchema1
  2. GO
  3.  
  4. CREATE TABLE TestSchema1.Test (id int)
  5. GO
  6.  
  7. INSERT TestSchema1.Test values(1);
  8. GO
  9.  
  10.  
  11. SELECT *
  12. FROM TestSchema1.Test;
  13. GO

Now let's create our second schema

  1. CREATE SCHEMA TestSchema2
  2. GO

In order to transfer/move an object from one schema to another you use ALTER SCHEMA <schema name> TRANSFER <object name>. Keep in mind that when you do this it drops all permissions.
All permissions associated with the securable will be dropped when the securable is moved to the new schema. If the owner of the securable has been explicitly set, the owner will remain unchanged. If the owner of the securable has been set to SCHEMA OWNER, the owner will remain SCHEMA OWNER; however, after the move SCHEMA OWNER will resolve to the owner of the new schema. The principal_id of the new owner will be NULL.

Here is the code to transfer the table to TestSchema2

  1. ALTER SCHEMA TestSchema2 TRANSFER TestSchema1.Test
  2. GO

Now let's try that select against TestSchema1 again

  1. SELECT *
  2. FROM TestSchema1.Test;

And as you would have guessed it errors out with the following message

Server: Msg 208, Level 16, State 1, Line 1 Invalid object name 'TestSchema1.Test'.

Running the select against TestSchema2 works fine

  1. SELECT *
  2. FROM TestSchema2.Test;



Contributed by: --SQLDenis 18:38, 15 April 2009 (GMT)

Part of SQL Server Admin Hacks

641 Rating: 2.8/5 (38 votes cast)