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
I have been involved in IT development for the last 10 years - a lot of it around desktop applications and telecoms.