1

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)
Liero
  • 25,216
  • 29
  • 151
  • 297
  • There is - but its a manual process - you have to write it yourself :) sorry – Dale K May 18 '22 at 09:03
  • do you really need to drop the view ? Are the changes not possible by alter view ? – GuidoG May 18 '22 at 09:04
  • The OP is altering the table, not the `VIEW`, @GuidoG . – Thom A May 18 '22 at 09:19
  • Possible, but tedious, particularly the part that has to script indexes, since there is no easy way to extract a definition for that from T-SQL (the view definition itself lives in `sys.sql_modules`, that's the easy bit). I wouldn't trust the results of a T-SQL script there, especially since there are many index options. I would look around for dedicated client tooling -- I don't know if `sqlpackage` can do it out of the box, but if not some custom code against DacFX could do it. Or SMO, if you're desperate enough. – Jeroen Mostert May 18 '22 at 10:54
  • I wouldn't try to do this all in T-SQL. Instead, I'd manually script out the view definition along with its indexes (as simple as "right-click → Script View as → Create to" in SSMS) and either inline that into your overall script or (my personal preference) put it into a separate file. – Ben Thul May 18 '22 at 14:59
  • @BenThul: The problem is, that when the view definition changes, the script will become obsolete, which already happed to me. Somebody turned regular view to indexed view and I recreated the view without indexes... Not mentioning possible changes in the view.. Unfortunately, not all older project has migration strategy. BTW, Script View as in SSMS does not include indexes for view :( – Liero May 18 '22 at 15:12
  • @JeroenMostert: So the problem narrows down to how to get indexes for the view? – Liero May 18 '22 at 15:14
  • Not necessarily. Dropping and recreating also drops all explicit permissions `GRANT`ed and `DENY`'d on the view, for example, so if those might exist you'd need to script them too. – Jeroen Mostert May 18 '22 at 15:35
  • Can't I just drop indexes, alter the view without schemabinding (string replace) and then recreate back? That is good enough for me. – Liero May 18 '22 at 15:42
  • That would work. Now all you have to do is script the indexes. As long as you only care about "boring" indexes (no special options, partitioning etc.), `sys.indexes`, `sys.index_columns` and `STRING_AGG` (or the `FOR XML` trick on older versions) allow you to craft a `CREATE INDEX` statement -- but it's not fun. – Jeroen Mostert May 18 '22 at 15:47
  • @Liero - a) presumably you're scoping your `alter table` in time so schema drift shouldn't be a concern; I'm not suggesting scripting it out, waiting for months, and then recreating it. b) SSMS certainly can script out indexes on the view - check Options → SQL Server Object Explorer → Scripting → Table and View Options → Script Indexes. – Ben Thul May 18 '22 at 21:09

0 Answers0