declare
  @table_schema varchar(100),
  @table_name varchar(100),
  @proc_schema varchar(100),
  @proc_name varchar(100),
  @sql varchar(500)
 
declare table_cursor CURSOR 
for
select TABLE_SCHEMA, TABLE_NAME from PIESCentral.INFORMATION_SCHEMA.TABLES
where TABLE_SCHEMA <> 'dbo'

open table_cursor

FETCH NEXT FROM table_cursor 
INTO @table_schema, @table_name

while @@FETCH_STATUS = 0
begin
  select @sql = 'alter schema dbo transfer [' + @table_schema + '].' + @table_name
  exec(@sql)

  FETCH NEXT FROM table_cursor   
  INTO @table_schema, @table_name
end

close table_cursor
deallocate table_cursor


declare proc_cursor CURSOR 
for
select SPECIFIC_SCHEMA, SPECIFIC_NAME from PIESCentral.INFORMATION_SCHEMA.ROUTINES
where SPECIFIC_SCHEMA <> 'dbo'

open proc_cursor

FETCH NEXT FROM proc_cursor 
INTO @proc_schema, @proc_name

while @@FETCH_STATUS = 0
begin
  select @sql = 'alter schema dbo transfer [' + @proc_schema + '].' + @proc_name
  exec(@sql)

  FETCH NEXT FROM proc_cursor   
  INTO @proc_schema, @proc_name
end

close proc_cursor
deallocate proc_cursor
