Check Validity of SQL Server Stored Procedures, Views and Functions

"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:

Collapse Copy Code

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"