Ah, but for astute readers! I received some email from a reader about a recent SQL Server Secrets article in UPDATE that I wrote. I wrote that you could use the following code in a script to update several views at once:
sp_refreshview vw_titlesbyauthor sp_refreshview vw_titlesauthor sp_refreshview vw_authors sp_refreshview vw_authorsbytitle
One reader pointed out that this code doesn't work. The reason is that this group of statements is a batch of SQL statements and Exec is the default command for the first statement in the batch. The second and following statements all fail. You can correct this code like this:
Exec sp_refreshview vw_titlesbyauthor Exec sp_refreshview vw_titlesauthor Exec sp_refreshview vw_authors Exec sp_refreshview vw_authorsbytitle
You can also use this format:
sp_refreshview vw_titlesbyauthor GO sp_refreshview vw_titlesauthor GO sp_refreshview vw_authors GO sp_refreshview vw_authorsbytitle GO
Inserting GO after each statement causes Query Analyzer to terminate the batch of commands and send it to SQL Server for processing. This allows each batch to use Execute as the default statement in each batch.
Several issues are important when you use GO. First, GO is not a SQL Server command. When the Query Analyzer and other tools see GO, they interpret it by sending the batch to SQL Server. SQL Server Books Online (BOL) says that the utilities never send a GO command to SQL Server and that GO is not a Transact SQL (T-SQL) command. So, what do you use GO for? You can use GO in the utilities to separate batches of commands. You can put GO in T-SQL code, such as a stored procedure, but I suspect that the SQL Server processor ignores GO.
To test this theory, I created the following SQL and executed it in Query Analyzer:
sp_refreshview titleview GO sp_refreshview sp_GetAuthorTitle GO
This command worked fine. Next, I tried this code in Query Analyzer:
CREATE PROCEDURE RecompViews AS sp_refreshview titleview GO sp_refreshview sp_GetAuthorTitle GO
This code fails because SQL Server strips the GO commands and because I didn't use Exec before the second sp_refreshview. This revised code works:
CREATE PROCEDURE RecompViews AS exec sp_refreshview titleview exec sp_refreshview sp_GetAuthorTitle
A second reader pointed out that you can automatically create SQL code to refresh views. The following SQL statement will automatically generate the code to refresh all the views in a database:
SELECT 'exec sp_refreshview ' + Name FROM sysObjects where xType = "v"
After this command completes, you can save the output to a .sql file, thereby starting a good maintenance utility. You would, of course, need to strip any extra characters from the output and remove any views that shouldn’t be processed as part of the script.