ALTER VIEW 從視圖中刪除索引

正如標題行所暗示的那樣：在具有索引的 VIEW 上使用 ALTER VIEW 語句將在沒有警告的情況下從 VIEW 中刪除此（全部？）索引。我希望 ALTER VIEW 語句失敗，通知我先刪除索引。

SQL SERVER 中是否有更改此行為的設置？還是在 SQL 2012 (SP3) 之後的版本中發生了變化？

您可以防止使用 DDL 觸發器更改索引視圖。但是實現有點複雜，因為 DDL 觸發器在視圖被更改並刪除索引之後執行，但在更改送出之前，您無法直接檢測到視圖之前有索引。

因此，您必須使用擴展屬性進行一些惡作劇，每當在視圖上創建索引時就將擴展屬性放置在視圖上。

例如：

create or alter trigger ddl_trig_prevent_alter_indexed_view   
on database  
for drop_index, create_index, alter_view
as   
begin
   --select eventdata().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')  , eventdata()

   declare @schemaName sysname, @objectName sysname, @eventType varchar(50), @targetObjectType varchar(50)
          ,@targetObjectName sysname, @targetObjectSchemaName sysname

   declare @e xml = eventdata();

   select @eventType = @e.value('(/EVENT_INSTANCE/EventType)[1]','varchar(50)'),
          @targetObjectType = @e.value('(/EVENT_INSTANCE/TargetObjectType)[1]','varchar(50)'),
          @targetObjectName = @e.value('(/EVENT_INSTANCE/TargetObjectName)[1]','sysname'),
          @schemaName = @e.value('(/EVENT_INSTANCE/SchemaName)[1]','sysname'),
          @objectName = @e.value('(/EVENT_INSTANCE/ObjectName)[1]','sysname')

   if @eventType = 'DROP_INDEX' and @targetObjectType = 'VIEW'
   begin
       --print @eventType
       set @targetObjectSchemaName = (select schema_name(schema_id) from sys.views where name = @targetObjectName)
       if exists( select * from fn_listextendedproperty ('HasIndex', 'SCHEMA', @targetObjectSchemaName, 'VIEW', @targetObjectName, NULL, NULL) )
          and not exists( select * from sys.indexes where object_id = object_id( concat(quotename(@targetObjectSchemaName),'.',quotename(@targetObjectName))) )
       begin
           exec sp_dropextendedproperty
                @name = N'HasIndex' 
               ,@level0type = N'Schema', @level0name = @targetObjectSchemaName
               ,@level1type = N'View', @level1name = @targetObjectName
           print 'Extended property HasIndex on view dropped.'
       end

   end
   else if @eventType = 'CREATE_INDEX' and @targetObjectType = 'VIEW'
   begin
       --print @eventType
       set @targetObjectSchemaName = (select schema_name(schema_id) from sys.views where name = @targetObjectName)
       if exists( select * from fn_listextendedproperty ('HasIndex', 'SCHEMA', @targetObjectSchemaName, 'VIEW', @targetObjectName, NULL, NULL) )
       begin
           exec sp_updateextendedproperty  
                @name = N'HasIndex' 
               ,@value = N'1' 
               ,@level0type = N'Schema', @level0name = @targetObjectSchemaName
               ,@level1type = N'View', @level1name = @targetObjectName
          print 'Extended property HasIndex on view updated.'
       end
       else
       begin
           exec sp_addextendedproperty  
                @name = N'HasIndex' 
               ,@value = N'1' 
               ,@level0type = N'Schema', @level0name = @targetObjectSchemaName
               ,@level1type = N'View', @level1name = @targetObjectName
          print 'Extended property HasIndex on view added.'
       end

   end
   else if @eventType = 'ALTER_VIEW'
   begin
     --print @eventType
     if exists( select * from fn_listextendedproperty ('HasIndex', 'SCHEMA', @schemaName, 'VIEW', @objectName, NULL, NULL) where value = '1' )
     begin
        ;throw 50001,'Cannot alter view with an index.  Drop the index first.', 1;
        rollback;
        return;
     end
   end
   else
   begin 
     ;throw 50001,'Unexpected event type in ddl trigger.', 1
   end
end

引用自：https://dba.stackexchange.com/questions/260901