Script to move all objects to a new schema for SQL Server
By: Brent Shaub | Updated: 2011-09-02 | Comments (4) | Related: More > Security
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.
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')
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.
- Application role
- Message Type
- Remote Service Binding
- Full Text Catalog
- Asymmetric Key
- Symmetric Key
- XML Schema Collection
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 '[email protected]+' transfer [dbo].'[email protected] 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 '[email protected]+' transfer [dbo].'[email protected] 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::'[email protected]) print 'Moved ' [email protected] 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)
- If you're using [dbo] for user-created objects, the database default, consider that a named schema requires another hoop to cross and could improve your environment's logical architecture.
- Use the above script to determine which user-created objects are candidates for migration.
- Become familiar with the securable/visibility hierarchy in SQL Server Books Online.
- Read about the User-Schema separation which began in SQL 2005 and affects system tables including sysusers and syslogins.
- Check out these related tips:
Last Updated: 2011-09-02
About the author
View all my tips