Script to move all objects to a new schema for SQL Server

Problem

I’m using a hosted database service and figuring out ways to best maintain my development and production environments. The host has a front-end to create databases through a website. After creating a database and completing a working script on Development, running this script on Production unexpectedly resulted in ‘Invalid object name’ errors. Turns out they were in a schema named after the user and not [dbo] as on my development machine. Is it possible to recreate the same schema on my machine? If so, can I move the objects from [dbo] into it? Are there ways to tell programmatically what objects are where? There’s a resounding ‘Yes’ to all three questions in this tip.

Solution

The solution begins by examining three system tables.

Initial Examination on Production

select * from sys.schemas
select * from sys.database_principals
select * from sys.objects
select count(1) as [21558_lib_remote_count]
from sys.objects
where schema_id = schema_id('[21558_lib_remote]')
 and type in ('U', 'V', 'P', 'FN')
select count(1) as dbo_count
from sys.objects
where schema_id = schema_id('dbo')
 and type in ('U', 'V', 'P', 'FN')

Schemas

SQL Server Schemas

Database Principals

SQL Server Database Principals

Object Counts

SQL Server Object Count

If executed on my development box, only dbo would have an object count. The schema [21558_lib_remote] didn’t exist. One work-around was to save the schema name prefix as a variable in the scripts (dbo for Development and [21558_lib_remote] on Production), but there’s clarity in simplicity, having one less setting to recall.

In reviewing Books Online, a page on Securables shed some light.

Server Securables:

  • Endpoint
  • Login
  • Database

Database Securables:

  • User
  • Role
  • Application role
  • Assembly
  • Message Type
  • Route
  • Service
  • Remote Service Binding
  • Full Text Catalog
  • Certificate
  • Asymmetric Key
  • Symmetric Key
  • Contract
  • Schema

Schema Securables:

  • Type
  • XML Schema Collection
  • Object

Schema Objects:

  • Aggregate
  • Constraint
  • Function
  • Procedure
  • Queue
  • Statistic
  • Synonym
  • Table
  • View

Tables in different schemas would be not visible to each other. How did this happen?

My attempt to recreate what happened using the hosting company’s front-end database-creation screen:

Proposed Creation Script on Production

CREATE LOGIN [21558_lib_remote] WITH PASSWORD = 'happytrails'
go
CREATE DATABASE [21558_lib_remote]
go
USE [21558_lib_remote]
go
CREATE USER [21558_lib_remote]
FROM LOGIN [21558_lib_remote]
go
CREATE SCHEMA [21558_lib_remote] AUTHORIZATION [21558_lib_remote]
go
ALTER USER [21558_lib_remote]
WITH DEFAULT_SCHEMA = [21558_lib_remote]
go

With the existing objects created in another schema, the route which conforms to current best practices was to move all the objects from my local [dbo] via ALTER SCHEMA.

I used the above proposed creation script to create a schema and user, then assign the objects their default schema. When I start a connection with that user, I’m taken to the new schema that matches the production environment. Now I can use the same script with a schema qualifier on both environments.

Migration Script of User-Created Objects from [dbo]

declare @theSchema sysname
declare @oneObj sysname, @aSQL nvarchar(1000)
set @theSchema = quotename('21558_lib_remote')
-- migration of user-defined objects in [dbo]
declare objCur cursor for
select quotename([name])
from sys.objects 
where (schema_id = schema_id('dbo') or schema_id = schema_id(@theSchema))
 and type in ('U', 'V', 'P', 'FN')
open objCur
fetch from objCur into @oneObj
while @@fetch_status=0 begin
 set @aSQL = 'alter schema '+@theSchema+' transfer [dbo].'+@oneObj
 print @aSQL
 exec sp_executeSQL @aSQL
 fetch next from objCur into @oneObj
end
close objCur
deallocate objCur
-- confirm by looking at the former and current schemas
select * from sys.objects 
where schema_id = schema_id(@theSchema) 
 or schema_id = schema_id('dbo')
order by schema_id

Migration Script of All User-Defined Types from [dbo]

declare @theSchema sysname
declare @oneObj sysname, @aSQL nvarchar(1000)
set @theSchema = quotename('21558_lib_remote')
-- migration of user-defined types in [dbo]
declare objCur cursor for
select quotename([name])
from sys.objects 
where (schema_id = schema_id('dbo') or schema_id = schema_id(@theSchema))
  and type in ('R', 'D') -- Rules and Defaults
open objCur
fetch from objCur into @oneObj
while @@fetch_status=0 begin
  set @aSQL = 'alter schema '+@theSchema+' transfer [dbo].'+@oneObj
  print @aSQL
  exec sp_executeSQL @aSQL
  fetch next from objCur into @oneObj
end
close objCur
deallocate objCur
declare @oneType varchar(50)
declare typCur cursor for
select name from sys.types 
where is_user_defined = 1
  and schema_id <> schema_id(@theSchema)
print 'Beginning migration'
open typCur
fetch from typCur into @oneType
while @@fetch_status = 0 begin
  exec('alter schema ' + @theSchema + ' transfer TYPE::'+@oneType)
  print 'Moved ' +@oneType
  fetch next from typCur into @oneType
end
close typCur
deallocate typCur
-- confirm by examining the schema_id field
select * from sys.types where is_user_defined = 1

The above script will work for both table and non-table user-defined types.

For completeness, the root Table Types will still reside in the SYS schema and need not be moved.

select * from sys.objects 
where object_id in 
 (select type_table_object_id from sys.table_types)

Next Steps

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *