StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSyntax check all stored procedures?
text
Body
copied!<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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload