title: Syntax check all stored procedures?
body: <p>i want to ensure that all stored procedures are still syntactically valid. (This can happen if someone renames/deletes a table/column).</p>
  
  <p>Right now my solution to check the syntax of all stored procedures is to go into Enterprise Manager, select the first stored procedure in the list, and use the procedure:</p>
  
  <ol>
  <li>Enter</li>
  <li>Alt+C</li>
  <li>Escape</li>
  <li>Escape</li>
  <li>Down Arrow</li>
  <li>Goto 1</li>
  </ol>
  
  <p>It works, but it's pretty tedious. i'd like a stored procedure called </p>
  
  <p><strong>SyntaxCheckAllStoredProcedures</strong></p>
  
  <p>like the other stored procedure i wrote that does the same thing for views:</p>
  
  <p><strong>RefreshAllViews</strong></p>
  
  <hr>
  
  <p>For everyone's benefit, RefreshAllViews:</p>
  
  <p><strong>RefreshAllViews.prc</strong></p>
  
  <pre><code>CREATE PROCEDURE dbo.RefreshAllViews AS
  
  -- This sp will refresh all views in the catalog. 
  --     It enumerates all views, and runs sp_refreshview for each of them
  
  DECLARE abc CURSOR FOR
       SELECT TABLE_NAME AS ViewName
       FROM INFORMATION_SCHEMA.VIEWS
  OPEN abc
  
  DECLARE @ViewName varchar(128)
  
  -- Build select string
  DECLARE @SQLString nvarchar(2048)
  
  FETCH NEXT FROM abc 
  INTO @ViewName
  WHILE @@FETCH_STATUS = 0 
  BEGIN
      SET @SQLString = 'EXECUTE sp_RefreshView '+@ViewName
      PRINT @SQLString
      EXECUTE sp_ExecuteSQL @SQLString
  
      FETCH NEXT FROM abc
      INTO @ViewName
  END
  CLOSE abc
  DEALLOCATE abc
  </code></pre>
  
  <hr>
  
  <p>For everyone's benefit, a stored procedure to mark all stored procedure as needing a recompile (marking a stored procedure for recompile will not tell you if it's syntactically valid):</p>
  
  <p><strong>RecompileAllStoredProcedures.prc</strong></p>
  
  <pre><code>CREATE PROCEDURE dbo.RecompileAllStoredProcedures AS
  
  DECLARE abc CURSOR FOR
       SELECT ROUTINE_NAME
       FROM INFORMATION_SCHEMA.routines
      WHERE ROUTINE_TYPE = 'PROCEDURE'
  OPEN abc
  
  DECLARE @RoutineName varchar(128)
  
  -- Build select string once 
  DECLARE @SQLString nvarchar(2048)
  
  FETCH NEXT FROM abc 
  INTO @RoutineName
  WHILE @@FETCH_STATUS = 0 
  BEGIN
      SET @SQLString = 'EXECUTE sp_recompile '+@RoutineName
      PRINT @SQLString
      EXECUTE sp_ExecuteSQL @SQLString
  
      FETCH NEXT FROM abc
      INTO @RoutineName
  END
  CLOSE abc
  DEALLOCATE abc
  </code></pre>
  
  <hr>
  
  <p>For completeness sake, the <strong>UpdateAllStatistics</strong> procedure. This will update all statistics in the database by doing a full data scan:</p>
  
  <p><strong>RefreshAllStatistics.prc</strong></p>
  
  <pre><code>CREATE PROCEDURE dbo.RefreshAllStatistics AS
  
  EXECUTE sp_msForEachTable 'UPDATE STATISTICS ? WITH FULLSCAN'
  </code></pre>