GeekZilla

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   
BEGIN            
    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 so.name like N'sp%' 
    and [name] < @LASTNAME 
    order by [name] desc

    IF @@rowcount=0  
        begin   
            SET @Done=1  
        end
    else
        begin
            exec ( 'print ''moving '+@LASTNAME+'''')
            exec ('ALTER SCHEMA '+@ToSchema+' TRANSFER '+@FromSchema+'.' + @LASTNAME  )
        end
END

Author Dave Howard

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

Comments

Add Comment

Latest Articles