Changing Schema for a group of objects

This script moves all of you stored procedures from one Schema to another.

This was really handy when using a Typed Dataset with SQL 2005 and stored procedures. The wizard creates stored procedures in the users Schema (and complains if it does not exist!)

There is a default From and To schema so you can just pass null in each time.

You could easily change this to apply the change to any type of object, such as tables.

CREATE PROCEDURE dbo.spMigrateStoredProcedures AS

DECLARE @FromSchema varchar(100)
DECLARE @ToSchema varchar(100)
SET @FromSchema = N'myDefaultSchema'
SET @ToSchema = N'dbo'

DECLARE @Done bit 
DECLARE @LASTNAME varchar(1000)
SET @Done=0 -- insert top of tree 
SET @LASTNAME = 'zzzzzzzz'

WHILE @Done=0   
    select top 1 @LASTNAME=[name] from sys.sql_modules sm 
    inner join sys.objects so on sm.object_id = so.object_id 
    where so.schema_id = schema_id(@FromSchema) and like N'sp%' 
    and [name] < @LASTNAME 
    order by [name] desc

    IF @@rowcount=0  
            SET @Done=1  
            exec ( 'print ''moving '+@LASTNAME+'''')
            exec ('ALTER SCHEMA '+@ToSchema+' TRANSFER '+@FromSchema+'.' + @LASTNAME  )
Author Dave Howard

I have been involved in IT development for the last 10 years - a lot of it around desktop applications and telecoms.


Giovana said:

This is what I was looking for. Thanks!!

16/Aug/2008 04:30 AM

Add Comment


Your comment has been received and will be shown once it passes moderation.