How do I change db schema to dbo

jonathan hall picture jonathan hall · Jul 18, 2009 · Viewed 208.9k times · Source

I imported a bunch of tables from an old sql server (2000) to my 2008 database. All the imported tables are prefixed with my username, for example: jonathan.MovieData. In the table properties it lists jonathan as the db schema. When I write stored procedures I now have to include jonathan. in front of all the table names which is confusing.

How do I change all my tables to be dbo instead of jonathan?

Current result: jonathan.MovieData

Desired result: dbo.MovieData

Answer

Remus Rusanu picture Remus Rusanu · Jul 18, 2009
ALTER SCHEMA dbo TRANSFER jonathan.MovieData;

See ALTER SCHEMA.

Generalized Syntax:

ALTER SCHEMA TargetSchema TRANSFER SourceSchema.TableName;