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

By: Brent Shaub   |   Updated: 2011-09-02   |   Comments (4)   |   Related: > Security

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.

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

Database Principals

Object Counts

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:

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.

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
    

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

• 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:
  • Breaking ownership chaining within a schema in SQL Server
  • SQL Server Security Tips

About the author

Brent Shaub has been creating, renovating and administering databases since 1998.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips