TSql – Recompile ALL Views and Stored proceedures and check for syntax and compile errors

Posted: 2012-12-07 in Sql
Tags: , ,

Made a schema change, added a column, want to check all stored procedures and views that they don’t have syntax or compile errors?

Try the below script its big but it works!!!

The output are the SP’s and Views that failed syntax or compile checks. Add this to a unit test and you’ll save yourself lots of run time headaches.

This is my  most precious script so please give me credit if you use it (even if you steal it to add a tweak or two and claim as your own)

 

BTW This script uses  FMTONLY which only work with SQL server 2008 R2 or below, you’ll need to upgrade it to sp_describe_first_result to run it in 2012

Cheers,

Choco

 --created by chocosmith.wordpress.com
 -- this could have been done with a single big query but this way its very easy to reuse certain components and tweak for something specific

--create temp table containing all sp, functions, and views we need to check
 IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects o WHERE o.xtype in ('U') and o.id = object_id(N'tempdb..#tempIdentityTables'))
 DROP TABLE #tempIdentityTables

CREATE TABLE #tempIdentityTables(
 id int not null,
 name [varchar](255) NOT NULL,
 identOn int default (0),
 ansiNullOn int default (0),
 [owner] VARCHAR(255),
 [objectType] VARCHAR(255),
 [createdDate] datetime,
 [definition] VARCHAR(MAX), --definition
 command VARCHAR(MAX), --definition
 results VARCHAR(MAX),
 processed int default (0),
 )

-- populate it with sp, views, and functions to be checked
 insert into #tempIdentityTables (id, name, identOn, ansiNullOn, [owner], [objectType], [createdDate], [definition])
 select
 o.id,
 o.name,
 OBJECTPROPERTY(id, 'ExecIsQuotedIdentOn') as quoted_ident_on,
 OBJECTPROPERTY(id, 'ExecIsAnsiNullsOn') as ansi_nulls_on,
 user_name(o.uid) owner ,
 --o.type,
 pr.type_desc ,
 pr.create_date ,
 mod.definition
 from sysobjects o
 INNER JOIN sys.sql_modules mod ON o.id = mod.object_id
 inner join
 ( select object_id, name, type_desc,create_date from sys.views
 union
 select object_id, name, type_desc,create_date from sys.procedures
 union
 SELECT object_id, name, type_desc,create_date FROM sys.objects WHERE type IN ('FN', 'IF', 'TF') -- scalar, inline table-valued, table-valued
 ) as pr on pr.object_id = o.id
 where category = 0

-- create the exec commands ready for the parse checks (unnessasary but good for debuggin, script intersection, code resuse)
 DECLARE @id int
 DECLARE @name VARCHAR(255)
 DECLARE @owner VARCHAR(255)
 DECLARE @identOn int
 DECLARE @ansiNullOn int
 DECLARE @SQL VARCHAR(MAX)
 DECLARE @definition VARCHAR(MAX)
 --set start condition cycle
 SELECT TOP 1 @id = id, @name = name , @owner = [owner], @identOn = identOn, @ansiNullOn = ansiNullOn, @definition =[definition] FROM #tempIdentityTables WHERE processed = 0

WHILE @id IS NOT NULL
 BEGIN
 set @SQL = ''

--if (@ansiNullOn > 0)
 --SELECT @SQL = @SQL +'SET ANSI_NULLS ON ' + CHAR(13)+CHAR(10)

--if (@identOn > 0)
 --SELECT @SQL = @SQL +'SET QUOTED_IDENTIFIER ON ' + CHAR(13)+CHAR(10)
 SELECT @SQL = @SQL +'SET FMTONLY ON ' + CHAR(13)+CHAR(10)
 --SELECT @SQL = @SQL +'SET NOEXEC ON ' + CHAR(13)+CHAR(10)

SELECT @SQL = @SQL + ' EXECUTE ('' '+ REPLACE(@definition,'''', '''''') +' '')' + CHAR(13)+CHAR(10)

--SELECT @SQL = @SQL +'SET NOEXEC OFF ' + CHAR(13)+CHAR(10)
 --SELECT @SQL = @SQL +'SET PARSEONLY OFF ' + CHAR(13)+CHAR(10)

-- mark as processed
 update #tempIdentityTables
 set command = @SQL, processed = 1
 where #tempIdentityTables.id = @id

--if no select the name would be last value and we'd get into an endless loop.... grrr
 set @id = null;
 SELECT TOP 1 @id = id, @name = name , @owner = [owner], @identOn = identOn, @ansiNullOn = ansiNullOn, @definition =[definition] FROM #tempIdentityTables WHERE processed = 0

END

-- run actual queries
 DECLARE @loopId int
 DECLARE @ExecSQL NVARCHAR(MAX)
 DECLARE @procName VARCHAR(255) = null
 DECLARE @objectType VARCHAR(255) = null

set @id = null
 select top 1 @loopId = id, @ExecSQL = command, @procName = name, @objectType = objectType from #tempIdentityTables where processed = 0 OR processed = -1 OR processed = 1

WHILE @loopId IS NOT NULL
 BEGIN

BEGIN TRY
 print 'Checking: ' + @procName
 EXEC sp_executesql @ExecSQL

update #tempIdentityTables
 set processed = 2
 where #tempIdentityTables.id = @loopId
 END TRY
 BEGIN CATCH

DECLARE @Failure varchar(MAX) = ''
 select @Failure = ERROR_MESSAGE();
 print 'Error detected: ' + @Failure
 update #tempIdentityTables
 set processed = -2,
 results = @Failure
 where #tempIdentityTables.id = @loopId
 END CATCH;

--if no results then name would be last value and we'd get into an endless loop.... grrr
 set @loopId = null;
 select top 1 @loopId = id, @ExecSQL = command, @procName = name, @objectType = objectType from #tempIdentityTables where processed = 0 OR processed = -1 OR processed = 1

END
 -- results returned are queries with errors
 select results, * from #tempIdentityTables where results is not null

Advertisements
Comments
  1. Zbigniew says:

    Hi, I used it, thanks! It works.
    But I’m not gonna tweak it. Just one-time validation done on my database.
    Cheers!

  2. Carl0s_ says:

    Excellent work, thanks so much! 🙂

  3. Mano says:

    but it is not identifying invalid table inside stored procedure ..

    • Choco Smith says:

      hi mano,

      I’ll take a look to see if anything obvious is wrong. I have been running this script for a while and it works. What version of SQL are you using? and the invalid table is it just a select reference?

      Also on about line 63 there are some commented out lines. depending on the version of sql you use you may have to switch from FMTONLY to NOEXEC.

      cheers
      Lachlan

  4. Sivaa says:

    Working great!

  5. Rick says:

    Absolutely fantastic script and functionality.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s