Updating Views - 24 Nov 1999

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:

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:

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:

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.