Generate Scripts automatically to Transfer any schema to dbo

set nocount on ;

drop table #t1

create table #t1

(

TABLE_SCHEMA varchar(100),

TABLE_NAME  varchar(100)

)

insert into #t1

select TABLE_SCHEMA,TABLE_NAME

 from information_schema.tables

where table_type='view'

DECLARE @RebuildIndexesSQL NVARCHAR(MAX)

Declare @schemaname nvarchar(max)

set @schemaname='Revoke'

--print @schematable

SET @RebuildIndexesSQL = ''

SELECT

@RebuildIndexesSQL = @RebuildIndexesSQL +

CASE

WHEN TABLE_SCHEMA = @schemaname

THEN CHAR(10) + 'ALTER Schema dbo Transfer ' +

QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME)

END

FROM #t1

where TABLE_SCHEMA=@schemaname

--print @RebuildIndexesSQL

DECLARE @StartOffset INT

DECLARE @Length INT

SET @StartOffset = 0

SET @Length = 4000

WHILE (@StartOffset < LEN(@RebuildIndexesSQL))

BEGIN

PRINT SUBSTRING(@RebuildIndexesSQL, @StartOffset, @Length)

SET @StartOffset = @StartOffset + @Length

END

PRINT SUBSTRING(@RebuildIndexesSQL, @StartOffset, @Length)