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
  • 1
    Views and the underlying tables are not normally changed or dropped frequently. What is the problem you are trying to solve?
    – kevinskio
    May 22 '19 at 13:01
0

Working under the assumption that you are dealing with some sort of code generation (mostly because this is the only time I have encountered this sort of issue), here is the general strategy I would employ:

But first,

All initial code should follow strict formatting. TSQL is on the best TSQL parser, so specific limitations need to be followed to simply the process. The limitations I am assuming are:

  1. The views do not have leading comments (if you want to tweak my example to incorporate them, I strongly suggest using either single line comments, as they are easier to work with).
  2. The create view will have "CREATE VIEW .view" as the first line, follow by "AS" on the second line, and the remaining code on the following lines (no extra spaces, tabs or CRLF)
  3. Unioned statements will be seperated by "UNION ALL", on it's own line.
  4. All the views that can be regenerated will be under the schema 'Regenerate'
  5. All table references will be in the form of "[schema].[table]"

With that in mind,

I suggest that you add a drop table trigger. Inside that tirgger I suggest that you get all views within the appropriate schema that have a reference to the table that was just dropped. I would fetch the definition for each and split out the view body based using "UNION ALL as the deliminator, using XML. I'd then remove all statements that have reference to the removed table. I would recombine the statements into a Create Views, then execute the whole set as a block of dynamic SQL.

Here is some sample code that follows that approach. Note, I didn't test the trigger, nor the final generated code (you get what you pay for [grin], so it may need some tweaking), but I simulate a trigger call and did a quick inspections of the resulting SQL.

CREATE TRIGGER db_Drop_Table
ON db
FOR DROP_TABLE
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @Schema SYSNAME = eventdata().value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
    DECLARE @Table SYSNAME = eventdata().value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname');

    DECLARE @Removed NVARCHAR(MAX) = CONCAT(QUOTENAME(@Schema), QUOTENAME(@Table))

    -- These may need to be tweak to accomdate how the underlying views are written
    DECLARE @UnionDelim NVARCHAR(100) = 'UNION ALL'
        , @AsDelim NVARCHAR(100) = CONCAT(CHAR(13), 'AS', CHAR(13))
        , @CreateView NVARCHAR(100) = 'CREATE VIEW '
        , @RegenerateSQL NVARCHAR(MAX);

    WITH ViewDefinitions AS (
        SELECT 
          OBJECT_DEFINITION(object_id) as ViewText
        FROM 
     sys.views as v
        WHERE OBJECT_SCHEMA_NAME(object_id) = 'Regenerate'
            AND OBJECT_DEFINITION(object_id) LIKE CONCAT('%', @UnionDelim ,'%')
     )
    , NormalReturnsViewDef AS (
        SELECT REPLACE(ViewText, CONCAT(CHAR(13), CHAR(10)), CHAR(13)) ViewText FROM ViewDefinitions
    )
    , ViewParts AS (
        SELECT 
            SUBSTRING(ViewText, CHARINDEX(@CreateView, ViewText)+ LEN(@CreateView) + 1,  CHARINDEX(@AsDelim, ViewText) - (CHARINDEX(@CreateView, ViewText)+ LEN(@CreateView)))  AS ObjectName
            , LEFT(ViewText,  CHARINDEX(@AsDelim, ViewText) + LEN(@AsDelim) - 1)  AS CreateText
            , RIGHT(ViewText, LEN(ViewText) - (CHARINDEX(@AsDelim, ViewText) + LEN(@AsDelim) - 1)) AS ViewBody 
        FROM NormalReturnsViewDef
    )
    , XmlViewBody AS (
    SELECT 
        ObjectName
        , CreateText
        ,  CAST(CONCAT('<def><statement><![CDATA[', REPLACE(ViewBody, @UnionDelim, ']]></statement><statement><![CDATA['), ']]></statement></def>') AS XML) as ViewBodyXml
    FROM ViewParts

    )
    , Statements AS ( 
         SELECT 
            ObjectName
            , CreateText
            , XmlStatementText.value('.', 'NVARCHAR(MAX)') StatementText
         FROM XmlViewBody
            CROSS APPLY ViewBodyXml.nodes('/def/statement') Statements(XmlStatementText)
    )
    , StatementsFiltered AS (
        SELECT * 
        FROM Statements
        WHERE StatementText NOT Like CONCAT('%',  @Removed, '%')
    )
    , RebuiltStatements AS (
        SELECT  
            CONCAT(
                'DROP VIEW IF EXISTS ', ObjectName, ';', CHAR(13),
                CreateText, CHAR(13),
                STRING_AGG(StatementText, @UnionDelim)

            ) as RecreateView
        FROM StatementsFiltered
        GROUP BY ObjectName, CreateText
    )
    SELECT @RegenerateSQL = STRING_AGG(RecreateView, CONCAT(CHAR(13), CHAR(13))) FROM RebuiltStatements

    EXEC (@RegenerateSQL)

END

Notes: The query optimizer will not produce a nice plan... however this should not be running often, so that is less of a concern. You may be able to reduce the number of CTEs, but I find it easier to follow the logic. This should work for 2008 R2 if you replace STRING_AGG with a UDF.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.