USP_RENAMESCHEMABOUND

CREATE procedure dbo.USP_RENAMESCHEMABOUND( @BEFORE nvarchar(max), @AFTER nvarchar(MAX))
as
begin

    declare @DROP nvarchar(max);
    declare @DEFINITION nvarchar(max);

    if not exists ( select name from sys.tables where name = 'SCHEMABOUNDVIEWSRENAMED') 
    begin
        CREATE TABLE [dbo].[SCHEMABOUNDVIEWSRENAMED](
            NAME nvarchar(max) collate DATABASE_DEFAULT not null
            ,DROPSTMT nvarchar(max) collate DATABASE_DEFAULT not null
            ,[DEFINITION] [nvarchar](max) collate DATABASE_DEFAULT NOT NULL 
            ,DROPPED int                     
            ) ON [DEFGROUP]
    end

    insert into [SCHEMABOUNDVIEWSRENAMED] (NAME, DROPSTMT, DEFINITION, DROPPED)
        select distinct 
            s.name
            ,'drop view ' + s.name + '.' + o.name as ObjName
            , m.definition  
            ,0
        from sys.sql_dependencies d
            join sys.objects o 
                    on o.object_id=d.object_id
            join sys.objects r 
                    on r.object_id=d.referenced_major_id
            join sys.sql_modules as m 
                    on o.object_id = m.object_id
            join sys.schemas as s
                    on o.schema_id = s.schema_id
            left outer join SCHEMABOUNDVIEWSRENAMED AS S2
            on S2.NAME = s.NAME
        where
            d.class=1
            and r.name = @BEFORE
            and o.type = 'V'
            and s2.NAME is null
    ;

    declare CMod cursor FAST_FORWARD for select DROPSTMT, DEFINITION from [SCHEMABOUNDVIEWSRENAMED] where DROPPED = 0
    ;    

    open CMod;
    fetch next from CMod into @DROP, @DEFINITION;
    while @@FETCH_STATUS = 0
    begin

        exec (@DROP);
        fetch next from CMod into @DROP, @DEFINITION;
    end;
    close Cmod;
    deallocate CMod;

    exec sp_rename @BEFORE, @AFTER;

    update dbo.[SCHEMABOUNDVIEWSRENAMED] set DROPPED = 1 where DROPPED = 0
end