SQL 2008: How do I change db schema to dbo

Question

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 ex: 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 1

ALTER SCHEMA dbo TRANSFER jonathan.MovieData;

See ALTER SCHEMA.

Answer 2

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/0a760138-460e-410a-a3c1-d60af03bf2ed.htm

ALTER SCHEMA schema_name TRANSFER securable_name

Answer 3

USE MyDB;
GO
ALTER SCHEMA dbo TRANSFER jonathan.MovieData;
GO

Ref: ALTER SCHEMA

Answer 4

You can run the following, which will generate a set of ALTER sCHEMA statements for all your talbes:

SELECT 'ALTER SCHEMA dbo TRANSFER ' + TABLE_SCHEMA + '.' + TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'jonathan'

You then have to copy and run the statements in query analyzer.

Here's an older script that will do that for you, too, I think by changing the object owner. Haven't tried it on 2008, though.

DECLARE @old sysname, @new sysname, @sql varchar(1000)

SELECT
  @old = 'jonathan'
  , @new = 'dbo'
  , @sql = '
  IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES
  WHERE
      QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''
      AND TABLE_SCHEMA = ''' + @old + '''
  )
  EXECUTE sp_changeobjectowner ''?'', ''' + @new + ''''

EXECUTE sp_MSforeachtable @sql

Got it from this site.

It also talks about doing the same for stored procs if you need to.

Answer 5

Way to do it for an individual thing:

alter schema dbo transfer jonathan.MovieData