Check Validity of SQL Server Stored Procedures, Views and Functions
26-Nov-0909 Leave a comment
"When I set out to do this, someone reminded me of the "check syntax" feature in SQL Enterprise manager. If I could replicate this functionality, this would be ideal, as I didn’t want to actually compile the database objects – just simulate compilation. So I pulled out SQL profiler and recorded the statements that are executed when you press the "Check Syntax" button. Interestingly what happens when you press this button is the following:
SET NOEXEC ON GO CREATE PROC....etc GO SET NOEXEC OFF GO SET PARSEONLY OFF GO
I had not come across these SET
commands before. Looking them up in BOL tells me what you have already guessed:
SET NOEXEC
"compiles each batch of Transact-SQL statements but does not execute them"SET PARSEONLY
"Checks the syntax of each Transact-SQL statement and returns any error messages without compiling or executing the statement"