Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mbevon
Starting Member
41 Posts |
 Posted - 2002-06-25 : 13:49:17
|
| HOw could i refresh all the views in a database?Thanks |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-06-25 : 13:52:16
|
quote:
HOw could i refresh all the views in a database?Thanks
If you mean recompile them, then you could use EM to generate SQL script to drop and recreate them.Jonathan Boott, MCDBA |
 |
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2002-06-25 : 14:01:43
|
| You can use sp_refreshview to refresh the meta-data of a view. You need to use a cursor or a WHILE loop to loop through sysobjects and run sp_refreshview on each view. Another approach is to generate the required script using the following command. Copy the output and run it:SELECT 'EXEC sp_refreshview ' + QUOTENAME(TABLE_NAME, '''') AS 'Refresh command'FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_TYPE = 'VIEW' AND TABLE_SCHEMA = 'dbo' AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0GOThe above script generates the commands required to refresh all dbo owned, user views in the current database and works in 7.0 and 2000.I have similar scripts at: http://vyaskn.tripod.com/generate_scripts_repetitive_sql_tasks.htm--HTH,Vyashttp://vyaskn.tripod.com |
|
|
|
|
|
|
Refresh Views