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.