SQL Server: Check references when altering function/procedure signatures

Question

One of my coworkers is working on a set of stored procedures, and changing the input parameters for one of them.

This will break any procedure that calls that one, so we need to find all those procedures. Right now, we manually call a dependency finder script that looks in system tables for procedures that reference the one we changed.

I was thinking it might be cool to put a trigger on a system table - when we change a stored procedure, we automatically recompile all the procedures that reference it, so we know when one of them breaks.

Is that possible in 2K8?

Answer 1

The simplest thing would be to script out the database, then do a search.

---

1. Right-click the database, and choose Tasks->Generate Scripts
2. Click "Next" if the welcome page appears
3. Select the database
4. Click "Next" again
5. Choose Database triggers, Stored procedures, Tables, User-defined functions, and Views, then click "Next"
6. On the next several pages, choose all objects of the selected type by using "Select All", then clicking "Next"
7. Choose "Script to file", and save the file where you can find it.
8. Click "Finish"

Once the script is created, you can use your favorite tools to search the script. You can even use regular expressions in SSMS with the "Find in Files" command.

Answer 2

Another option (if this would be ok with your requirements) is to change the existing procedure so that the old existing parameter is optional (just add " = Null" at the end) add the new parameter as optional as well, and rewrite the sProc sp that it will use one or the other depending on which one is passed... Then the old code that calls it with the old parameter will continue to function as it used to...

Answer 3

You cannot add a trigger to a system table, but you can create a DDL trigger that will fire on the ALTER PROC statement - example:

ALTER TRIGGER DDL_PROC
ON DATABASE
FOR ALTER_PROCEDURE
AS
DECLARE @data XML, @ObjectName sysname, @ParamCount int
SET @data = EVENTDATA()
SET @ObjectName = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')

PRINT 'You have changed procedure: ' + @ObjectName
SELECT @ParamCount = COUNT(*) FROM sys.parameters
WHERE object_id = OBJECT_ID(@ObjectName)
RAISERROR('This procedure now has %i parameter(s)',0,0,@ParamCount)
GO

I'm not sure how to get the previous parameter list or if it's even possible - does anybody know?

I would agree with Charles' suggestion to make new parameters optional if possible - existing code will not break and you only have to find the references if you must add the parameter to the call.

Scripting out the database seems like the long way around. A simple query such as the following should find all references to your proc (within your database):

SELECT so.name, so.type_desc
FROM sys.all_objects so
    JOIN sys.all_sql_modules sm ON so.[object_id] = sm.[object_id]
WHERE sm.[definition] LIKE '%<proc name>%'

This is the same thing, but will also work in previous versions of SQL server:

SELECT so.name, so.type
FROM syscomments sc
    JOIN sysobjects so ON sc.id = so.id
where text like '%<proc name>%'

Hope this helps,

MDD