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:
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:
- 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).
- 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)
- Unioned statements will be seperated by "UNION ALL", on it's own
- All the views that can be regenerated will be under the schema 'Regenerate'
- 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
SET NOCOUNT ON;
DECLARE @Schema SYSNAME = eventdata().value('(/EVENT_INSTANCE/SchemaName)', 'sysname');
DECLARE @Table SYSNAME = eventdata().value('(/EVENT_INSTANCE/ObjectName)', '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 (
OBJECT_DEFINITION(object_id) as ViewText
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 (
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
, XmlViewBody AS (
, CAST(CONCAT('<def><statement><![CDATA[', REPLACE(ViewBody, @UnionDelim, ']]></statement><statement><![CDATA['), ']]></statement></def>') AS XML) as ViewBodyXml
, Statements AS (
, XmlStatementText.value('.', 'NVARCHAR(MAX)') StatementText
CROSS APPLY ViewBodyXml.nodes('/def/statement') Statements(XmlStatementText)
, StatementsFiltered AS (
WHERE StatementText NOT Like CONCAT('%', @Removed, '%')
, RebuiltStatements AS (
'DROP VIEW IF EXISTS ', ObjectName, ';', CHAR(13),
) as RecreateView
GROUP BY ObjectName, CreateText
SELECT @RegenerateSQL = STRING_AGG(RecreateView, CONCAT(CHAR(13), CHAR(13))) FROM RebuiltStatements
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.