Tuesday, August 7, 2012

T-SQL script for refreshing all views in a database


There are scenarios when SELECT * is used in views - it's not uncommon in a relational data warehouse.

If WITH SCHEMABINDING is not specified when the view is created, then changing the tables behind the views might corrupt the mapping of the table columns to the output columns, which can have a very confusing result.

A stored procedure called sp_refreshview can be called to correct the mappings. But if you don't have time to look for the corrupted one, you can fabricate a script to loop through all of them.

Or, you can download my one and simply run it. I posted a script to TechNet Galleries, which refreshes all views in a database, regardless of their schema.

Basically it's a cursor solution, calling sp_refreshview for each view in the database.

You can find it here.