Stephen Shakespeare

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

Actually this is because Objects are cached. You can refresh cache using

Edit -> Intellisense -> Refresh Local Cache (CTRL+Shift+R)

CREATE PROCEDURE [dbo].[uspViewsSchemaBound]
(@DatabaseName nvarchar(50))
AS

@SQL nvarchar(200)
SET @SQL = ‘SELECT * FROM ‘ + @DatabaseName + ‘.INFORMATION_SCHEMA.VIEWS WHERE VIEW_DEFINITION LIKE ”%WITH SCHEMABINDING%”’
EXECUTE (@SQL)