mssqltips logo

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

By:   |   Updated: 2011-09-02   |   Comments (4)   |   Related: More > 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.

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 '[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)
Next Steps


Last Updated: 2011-09-02


get scripts

next tip button



About the author
MSSQLTips author Brent Shaub Brent Shaub has been creating, renovating and administering databases since 1998.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Tuesday, September 06, 2011 - 4:10:24 AM - Brent Shaub Back To Top

Hi Usman, I bring an update.  There is a different syntax for moving types between schemas.  Try this:

ALTER SCHEMA target_schema TRANSFER TYPE::target_type

Works with both table and non-table types.  The main tip can be used for DEFAULTS and RULES.  This comment replaces the above icomment of creating a new type in the target schema, etc.


Tuesday, September 06, 2011 - 2:46:37 AM - Usman Butt Back To Top

 

Hi Brent,

 

Your detailed response is highly appreciated. Although there are instances like in our environment, where dbo dependency is not required to keep every schema autonomous and the sysadmins group can drop and create any object in dbo schema....I have got your point which is very much valid unless an exceptional case like I have to cope with. Waiting for the next part. Cheers.

 

Best Regards,
Usman

 


Tuesday, September 06, 2011 - 12:37:27 AM - Brent Shaub Back To Top

Hi Usman, I had a chance to dig into your query for User-defined types.  By adding 'D' and 'R' into the "where type in ()" clause for the cursor, you'll be able to move the Defaults and Rules that the user-defined types are based on into the target schema.

The types themselves are tricky to move, and I have not yet been able to; the code that works to move schemas for objects doesn't work on types too.  I say the migration is tricky, because to drop a type it must not be used.  So it seems the easiest is to:

  1. Create the new type in the target schema (it can match the existing type in dbo)
  2. Bind to the default and rule which will migrate into the schema fine ('D', 'R')
  3. ALTER columns to use it as their datatypes
  4. DROP TYPE in the original schema

All this said, having the TYPE in DBO and the table/columns in specific schemas will work fine with inserts, updates, etc.  This allows centralized architecture of datatypes.  Because of this, keeping the Default and Rule in DBO makes the most sense to me so they are all centralized and visible together.

Next I'll answer the Table-valued functions part.


Monday, September 05, 2011 - 2:07:05 AM - Usman Butt Back To Top

Hi,

Thanks for the script. But I was wondering the script is not moving all the objects. e.g. no mention of Table Valued functions, User defined data types etc. Is there any limitation for above mentioned missing objects? Thanks in advance.

 

Best Regards,
Usman



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools