How do I change db schema to dbo
ALTER SCHEMA dbo TRANSFER jonathan.MovieData;
See ALTER SCHEMA.
Generalized Syntax:
ALTER SCHEMA TargetSchema TRANSFER SourceSchema.TableName;
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.
USE MyDB;
GO
ALTER SCHEMA dbo TRANSFER jonathan.MovieData;
GO
Ref: ALTER SCHEMA