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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: