0

I want to alter the view for the drop tables.

For an example:

View:

create view View1 as 
select * from table1
union all
select * from table2
union all
select * from table3
union all
select * from table4

I droped table1 and want create MasterView contains View1.

Note: Need to alter view view1 as table1 has been droped.

My try:

create procedure udsp_createallview
as
begin

declare @sql varchar(max) = ''
declare @tbl varchar(max) = ''
declare @view varchar(max) = ''
declare @create_view varchar(max) = ''
declare @query nvarchar(max) = ''
declare @create_view1 varchar(max) = ''
    begin try

        set @sql = 'create view dbo.MasterView AS
                    select * from View1 
                    union all
                    select * from View2';

        print(@sql);
        exec(@sql);

    end try
    begin catch

        select @tbl = replace(SUBSTRING(ERROR_MESSAGE(),CHARINDEX('''',ERROR_MESSAGE()),LEN(ERROR_MESSAGE())),'''.',''''),
               @view = ERROR_PROCEDURE();          

    end catch

    if @tbl <> '' and @view <> ''
    begin

        select @create_view = VIEW_DEFINITION from INFORMATION_SCHEMA.VIEWS WHERE Table_name = @view 

        --set @create_view = 'replace(replace('+@create_view+','+@tbl+','''')';
        set @query = 'select @Result = replace('''+@create_view+''','+@tbl+',''''),''create'',''alter'')';

        --exec(@query);
        exec sp_executesql @query, N'@Result varchar(max) out', @create_view1 out
        print(@create_view1);

        print(@tbl);
        print(@view);
    end

end
go

exec udsp_createallview;
go

Unable to remove rest of the select statement part. Sometimes select statement may come with where clause.

  • 1
    Views and the underlying tables are not normally changed or dropped frequently. What is the problem you are trying to solve? – kevinsky May 22 at 13:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.