Which of your Stored Procedures are no longer Valid?

Posted on April 6, 2008

6


The script presented in this article is for SQL Server 2005 or SQL Server 2008.

Here’s the problem; SQL Server has no problem when you create a Stored Procedure that references a non-existing table. It has no problem when you use a column that is simply not there. It is not validated until it’s executed. All that is checked (parsed) is the syntax. This is made worse when the structure of your database changes or people within your organisation come and go and those one-off or test procedures are left in your database.

How much dead wood is left in your database? Who wants to remove a procedure, it may be used by some process somewhere but no one is actually that sure. Who is going to take the risk?.

Well, there are a number of ways to see procedures that are no longer in use – you can check the procedure cache and list out procedures that dont exist in the cache. This is quite a quick and simple way to see procedures that have not been used in a while. However, there might be procedures that are still valid – they still may work if run, whenever that may be. They still might be valid.

What we really want is a quick way to actually check the procedures to see if they still work against our current structure. We want to do this without actually executing them and potentially causing a 3 year old bug to appear or wipe out some table because a particular procedure was created to do just that, once , at some point in our application’s history.

As it turns out, it isn’t that straightforward but it can be done.

What I am going to show you here is a script which will obtain a list of your procedures, grab their parameters and then check each procedure , one at a time; if it turns out they are not valid they will be added to a temp table for listing at the end of the script.

First, I will outline the key steps. Then I will show the full script you can use. The first part of process is to list the procedures , their parameters and dummy (NULL) assignment. This will be used to execute the procedures.

SELECT [name]=OBJECT_NAME(SM.[object_id]), [type]=SO.[type], SO.[type_desc]
,[params] = (
SELECT
(
SELECT CONVERT(xml,(
SELECT STUFF((
SELECT ', '+[name]+'=NULL' AS [text()]
FROM sys.parameters
WHERE [object_id] = SM.[object_id]
FOR XML PATH('') ),1,1,'')))
FOR XML RAW, TYPE
).value( '/row[1]', 'varchar(max)' )
)
FROM sys.sql_modules SM JOIN sys.objects SO ON SO.[object_id] = SM.[object_id]
WHERE SO.[is_ms_shipped] = 0
AND SO.[type] = 'P'

Next, when it has the list of procedures it creates a test harness for the procudure. E.g.

CREATE PROCEDURE up_sqldev_SPCheck_exec
AS
EXEC
up_TtDay_insert @Id=NULL;
SET FMTONLY ON;
EXEC up_TtDay_insert @Id=NULL; SET FMTONLY OFF

Then, finally it runs the test harness procudure; removes it and then moves onto the next procedure. Any errors are logged in a temporary table.

Use this script without warranty, we have found it very useful but please use wisely and in non-production environments until you are personally happy with it’s output:

SET NOCOUNT ON;
DECLARE @tblInvalid TABLE ( -- invalid objects
[type_desc] NVARCHAR(60), [name] sysname, [error_number] INT, [error_message] NVARCHAR(2048), [type] CHAR(2) );
DECLARE @type CHAR(2); -- object type
DECLARE @type_desc NVARCHAR(60); -- object type description
DECLARE @name sysname; -- object name
DECLARE @params NVARCHAR(MAX); -- parameters
DECLARE @sql NVARCHAR(MAX); -- dynamic sql
DECLARE curObjs CURSOR LOCAL FAST_FORWARD FOR
SELECT
[name]=OBJECT_NAME(SM.[object_id]), [type]=SO.[type], SO.[type_desc]
,[params] = (
SELECT
(
SELECT CONVERT(xml,(
SELECT STUFF((
SELECT ', '+[name]+'=NULL' AS [text()]
FROM sys.parameters
WHERE [object_id] = SM.[object_id]
FOR XML PATH('') ),1,1,'')))
FOR XML RAW, TYPE
).value( '/row[1]', 'varchar(max)' )
)
FROM sys.sql_modules SM JOIN sys.objects SO ON SO.[object_id] = SM.[object_id]
WHERE SO.[is_ms_shipped] = 0
AND SO.[type] = 'P'
OPEN curObjs;
WHILE (1=1)
BEGIN
FETCH
NEXT
FROM curObjs INTO @name, @type, @type_desc, @params;
IF (@@FETCH_STATUS != 0) BREAK;
IF OBJECT_ID('up_sqldev_SPCheck_exec',N'P') IS NOT NULL
DROP PROCEDURE up_compare_dbs_temp_invalid_sp_check;
SET @sql = N'

CREATE PROCEDURE up_sqldev_SPCheck_exec

AS

EXEC '+ @name +' '+ @params +';'
PRINT @sql
EXEC (@sql);
BEGIN TRY
BEGIN TRANSACTION;
SET @sql = N'SET FMTONLY ON; EXEC '+ @name +' '+ @params +'; SET FMTONLY OFF;';
PRINT @sql;
EXEC (@sql);
ROLLBACK TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
IF ( ERROR_NUMBER() != 0 )
INSERT INTO @tblInvalid
SELECT @type_desc, @name, ERROR_NUMBER(), ERROR_MESSAGE(), @type;
END CATCH
IF OBJECT_ID('up_sqldev_SPCheck_exec',N'P') IS NOT NULL
DROP PROCEDURE up_sqldev_SPCheck_exec;
END;
CLOSE curObjs;
DEALLOCATE curObjs;
SET NOCOUNT OFF;
SELECT [type_desc], [name], [error_number], [error_message]
FROM @tblInvalid
ORDER BY CHARINDEX( [type], ' U V PK UQ F TR FN TF P SQ ' ), [name];
GO

I want to give most of the credit for this script to Hue Holleran, a personal friend of mine who regularly presents at SQL Server themed events. For those of you who caught his presentation on SQLCMD at the March 2008 SQLBits, he won presenter of the day. Myself and Hue bounced around various of versions of this until Hue came up with the winning script. This is also forming part of a bigger script Hue is working on , so stay tuned for an follow up post that I hope Hue does to explain this.

If you have any suggestions for improvement or have any general questions about this please comment on this blog entry.

Advertisements
Posted in: Article, SQL, Tools