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