How to drop and recreate indexed view without hardcoding the CREATE VIEW definitiotion

334 Views Asked by At

I have couple of indexed views (with schema binding and index) in my database.

I want to alter some some columns used by the view, but I get:

The object 'MyIndexedView' is dependent on the column 'MyColumn'.

ALTER TABLE ALTER COLUMN MyColumn because one or more objects access this column.

Is there a script that would allow me to:

  1. drop the view
  2. execute my ALTER TABLE scripts
  3. recreate the view and indexes

Something like

-- 0. catch the schema and indexes
declare @definitionBackup VARCHAR(MAX) = getDefinitionWithIndexes('dbo', 'MyIndexedView');

-- 1. drop
DROP VIEW dbo.MyIndexedView

-- 2. update
ALTER TABLE .....

-- 4. recreate
EXEC (@definitionBackup) 
0

There are 0 best solutions below