Sql-server – T-SQL to check whether or not a indexed view can be created on tableX

dependenciesmaterialized-viewsql serversql-server-2005truncate

After applying my database changes on the test environment, the test team starts testing their processes,
one of the things I get is a truncate table error because the table is used in an indexed view.

    (1 row(s) affected)
    ProductIconTypes ending
    ProductImageCategories starting
    Msg 3729, Level 16, State 2, Line 1
    Cannot TRUNCATE TABLE 'dbo.ProductImageCategories' 
because it is being referenced by object 'VWProductImageGetByStyle'.

Questions:

1) next time I want to put an indexed view on one of my databases
what exactly I should be checking?

2) Can I get this done through my favourite route (T-SQL)?

I would not mind powershell either.

Unfortunately on these servers in question we are still on SQL Server 2005 AND SQL Server 2008 R2.

A Comment:

These versions impose a lot of limitations on things that we can do as alternatives. Filtered indexes for instance are not available on SQL Server 2005.

I am actually looking for a way if possible to find out what would stop working if I created this indexed view. In my example, the truncate tables could no longer work.

Best Answer

Since creating an indexed view is essentially creating a view WITH SCHEMABINDING you can no longer execute any DDL statements against the underlying objects which would affect the view definition. In this case TRUNCATE (a DDL operation) is viewed as view-affecting.

Detecting any queries that would break when you create views with schemabinding would equal detecting queries that execute DDL statements against the underlying tables and that depends on where your queries are coming from, but you basically need to look for ALTER TABLE|VIEW <myobject> for all objects used in your view or TRUNCATE TABLE <mytable>

If all your access is coming from stored procedures you could search them using this method.

If all your queries are generated from your application and you have the source code of it, you could search the source code or source control repository.

If you have a precompiled 3rd party app you could get lucky by using strings which also has a windows build.

If your applications are using ORM like frameworks you could be out of luck.

Related Question