Block altering indexed views.

------------------------------------------------------------------------------------------------------------------
-- Part one : Create the recording stored procedure
------------------------------------------------------------------------------------------------------------------
-- This stored procedure should be added to a SQL Agent job an run at least once a day.
CREATE PROC usp_RecordIndexedViews
(
@DBNameNVarchar(128) = NULL -- The database where table IndexView can be found or will be created in.
)
AS
BEGIN

/*
Created by: Dennis Post 13-08-2015
Description: Record the names of each indexed view in each database in the instance.
: This will be the reference point for DDL trigger DBTrg_BlockAlterIndexOnIndexedViews
*/
-- Parameters (Testing)
--DECLARE@DBNameNVarchar(128) = NULL

-- Internal Variables
DECLARE@SQLNVarchar(1000)

-- Default to creating the IndexView table in the same database as this store procedure.
IF @DBName IS NULL 
SET @DBName = DB_NAME() 

-- Build dynamic tsql to create table IndexedView if needed.
SET@SQL = N'
IF OBJECT_ID(''' + @DBName + N'.dbo.IndexedViews'') IS NULL
BEGIN
CREATE TABLE ' + @DBName + N'.dbo.IndexedViews
(
DBNameVarchar(128) NOT NULL,
SchemaNameVarchar(128) NOT NULL,
ViewNameVarchar(128) NOT NULL,
UpdatedSmallDateTime NOT NULL
)
END'
EXEC sp_ExecuteSQL @SQL

-- Build dynamic tsql to insert or update table IndexedView, ignoring system DBs.
SET @SQL = N'USE [?]; 
IF DB_ID() > 4
MERGE[' + @DBName + N'].dbo.IndexedViews Trgt
USING(
SELECTDISTINCT 
DBName= DB_NAME() 
, SchemaName= S.Name 
, ViewName= V.Name
, Updaed= GETDATE()
FROMsys.Views V
INNER JOIN sys.Schemas S
ON V.[schema_id] = S.[schema_id]
INNER JOIN sys.Indexes I
ON V.[object_id] = I.[object_id]
WHEREV.is_ms_shipped = 0
) Src
ONTrgt.DBName= Src.DBName
ANDTrgt.SchemaName = Src.SchemaName
AND Trgt.ViewName= Src.ViewName
WHENMATCHED THEN 
UPDATE SET Updated = GETDATE()
WHENNOT MATCHED BY TARGET THEN 
INSERT
VALUES(DBName, SchemaName, ViewName, GETDATE());'
EXEC sp_MSForEachDB @SQL
END
GO

------------------------------------------------------------------------------------------------------------------
-- Part two : Generating DDL Trigger code
------------------------------------------------------------------------------------------------------------------
-- Parameters
DECLARE @DBNameNVarchar(128)= NULL-- The database that has table IndexedView
, @AllDBsBit= 0-- Whether or not to generate DDL trigger creation TSQL for each DB in the instance. 0 = The current database

-- Internal Variables
DECLARE@SQLNVarchar(4000)
, @SQL2NVarchar(4000)

IF @DBName IS NULL 
SET @DBName = DB_NAME()

SET @SQL = N'USE [?];
GO

CREATE TRIGGER [DBTrg_BlockAlterIndexOnIndexedViews] ON DATABASE
FOR ALTER_VIEW
AS
BEGIN
-- Created by: Dennis Post 13-08-2015
-- Description: Works in conjunction with table IndexedViews 
--: to ensure that indexed views are not accidentally altered.
--: Altering an indexed view will drop all the indexes on it without warning.

DECLARE @SchemaNameVarchar(128),
@ViewNameVarchar(128),
@ErrMsgVarchar(2000)
 
SELECT@Schemaname= EVENTDATA().value(''(/EVENT_INSTANCE/SchemaName)[1]'',''varchar(128)''),
@ViewName= EVENTDATA().value(''(/EVENT_INSTANCE/ObjectName)[1]'',''varchar(128)'')

IF EXISTS 
(
SELECT1 
FROM[' + @DBName + '].dbo.IndexedViews 
WHEREDBName = DB_NAME() 
AND SchemaName = @SchemaName 
AND ViewName = @ViewName
)
BEGIN
SET @ErrMsg =''ALTER VIEW not allowed.'' + CHAR(13) + 
''Accoording to table ' + @DBName + '.dbo.IndexedViews there are one or more indexes on this view.'' + CHAR(13) +
''If this is no longer the case then manually delete the view from the table.'' + CHAR(13) +
''Or you can temporarily disable this trigger and run your ALTER statement again.''

RAISERROR(@ErrMsg, 15, 15)
ROLLBACK
END

END
GO

ENABLE TRIGGER [DBTrg_BlockAlterIndexOnIndexedViews] ON DATABASE
GO'

-- Prepare the TSQL for sp_MSForEachDB execution
SET @SQL2 = N'USE[?]; IF DB_ID() > 4 PRINT N''' + REPLACE(@SQL, '''', '''''') + ''''

-- Generate the script(s) based on parameter @AllDBs
IF @AllDBs = 1
EXEC sp_MSForEachDB @SQL2
ELSE
BEGIN
SET @SQL2 = REPLACE(@SQL2, '[?]', '[' + DB_NAME() + ']')
EXEC sp_ExecuteSQL @SQL2
END

------------------------------------------------------------------------------------------------------------------
-- Part three : Test and see how it works.
------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID('vTestIndexedView', 'V') IS NOT NULL
DROP VIEW vTestIndexedView

IF OBJECT_ID('IndexViewTestTable') IS NOT NULL
DROP TABLE IndexViewTestTable
GO

SELECTDISTINCT 
DBName= DB_NAME()
, SchmName= OBJECT_SCHEMA_NAME(C.[object_id])
, TblName= OBJECT_NAME(C.[object_id])
INTOIndexViewTestTable
FROMsys.Columns C
GO

CREATE VIEW vTestIndexedView
WITH SCHEMABINDING -- Must use schemabing to create an index
AS
SELECTDBName
, SchmName
, TblName
FROMdbo.IndexViewTestTable -- Must be a 2 part name
GO

-- Must have a unique clsutered index
CREATE UNIQUE CLUSTERED INDEX CI_vTestIndexedView_FourPartNames ON vTestIndexedView (DBName, SchmName, TblName)
GO

-- Show that the view is indeed indexed
SELECTName 
FROMsys.Indexes I
WHEREOBJECT_NAME(I.[object_id]) = 'vTestIndexedView'

-- Insert all the Indexed view names. Assuming this is the correct database to use.
EXEC usp_RecordIndexedViews @DBName = NULL
GO

-- check out the indexed views that were recorded. Assumeing correct database.
SELECT * FROM IndexedViews
GO
-- This will raise the error from the DDL trigger. 
ALTER VIEW vTestIndexedView
WITH SCHEMABINDING -- Must use schemabing to create an index
AS
SELECTDBName
, SchmName
, TblName
FROMdbo.IndexViewTestTable -- Must be a 2 part name
GO
/* Raised error:
Msg 50000, Level 15, State 15, Procedure DBTrg_BlockAlterIndexOnIndexedViews, Line 73
ALTER VIEW not allowed.
Accoording to table <DBName>.dbo.IndexedViews there are one or more indexes on this view.
If this is no longer the case then manually delete the view from the table.
Or you can temporarily disable this trigger and run your ALTER statement again.
Msg 3609, Level 16, State 2, Procedure vTestIndexedView, Line 42
The transaction ended in the trigger. The batch has been aborted.
*/
-- Do what the message suggests and delete the indexedview from table IndexedViews
DELETE FROM IndexedViews WHERE ViewName = 'vTestIndexedView'
GO

-- This will no longer generate the error.
ALTER VIEW vTestIndexedView
WITH SCHEMABINDING -- Must use schemabing to create an index
AS
SELECTDBName
, SchmName
, TblName
FROMdbo.IndexViewTestTable -- Must be a 2 part name
GO

-- Show that the view lost its index
SELECTName 
FROMsys.Indexes I
WHEREOBJECT_NAME(I.[object_id]) = 'vTestIndexedView'

-- Clean up
IF OBJECT_ID('vTestIndexedView', 'V') IS NOT NULL
DROP VIEW vTestIndexedView

IF OBJECT_ID('IndexViewTestTable') IS NOT NULL
DROP TABLE IndexViewTestTable

------------------------------------------------------------------------------------------------------------------
-- Part four : Remove all this alter indexed view blocking nonsense.
------------------------------------------------------------------------------------------------------------------
EXEC sp_MSForEachDB 'USE [?]; 
BEGIN
IF EXISTS
(
SELECTTOP (1) *--1 
FROMsys.Triggers 
WHEREName = ''DBTrg_BlockAlterIndexOnIndexedViews''
AND parent_class_desc = ''DATABASE''
)
DROP TRIGGER [DBTrg_BlockAlterIndexOnIndexedViews] ON DATABASE

IF OBJECT_ID(''usp_RecordIndexedViews'', ''P'') IS NOT NULL
DROP PROC usp_RecordIndexedViews

IF OBJECT_ID(''IndexedViews'', ''U'') IS NOT NULL
DROP TABLE IndexedViews

IF OBJECT_ID(''vTestIndexedView'', ''V'') IS NOT NULL
DROP VIEW vTestIndexedView

IF OBJECT_ID(''IndexViewTestTable'') IS NOT NULL
DROP TABLE IndexViewTestTable
END'