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.
Subscribe to:
Post Comments (Atom)
Good stuff again, Zoli! :)
ReplyDeleteBut I would suggest to add this two conditions to the WHERE clause of the query that fill the cursor:
OBJECTPROPERTY(object_id, 'IsSchemaBound') <> 1
AND OBJECTPROPERTY(object_id, 'IsMsShipped') <> 1
The first (IsSchemaBound = false) restriction is really important. If the db contains views WITH SCHEMABINDING, we will get error without this.
Thanks, I've changed it.
ReplyDeletehttps://saglamproxy.com
ReplyDeletemetin2 proxy
proxy satın al
knight online proxy
mobil proxy satın al
N5MGG