SQL Server: check references when changing function / procedure signatures
One of my coworkers is working on a set of stored procedures and is changing the input parameters for one of them.
This will break any procedure that calls this, so we need to find all those procedures. Right now, we're manually calling a dependent finder script that looks at the system tables for procedures that reference the one we changed.
I thought it would be great to put a trigger on a system table - when we change a stored procedure, we will automatically recompile all procedures that reference it, so we know when one of them breaks.
Is this possible in 2K8?
source to share
You cannot add a trigger to the system table, but you can create a DDL trigger that will run in an 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 this is possible - does anyone know?
I would agree with Charles' suggestion to make the new parameters optional if possible - the existing code won't break and you'll need to find references if you must add a parameter to the call.
Database scripting seems like a long way to go. A simple query like the following should find all references to your proc (in 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, but will work in previous versions of SQL Server too:
SELECT so.name, so.type
FROM syscomments sc
JOIN sysobjects so ON sc.id = so.id
where text like '%<proc name>%'
Hope it helps,
MRS
source to share
The simplest thing would be to script from the database, then do a search.
- Right click the database and select "Tasks-> Generate Scripts"
- Click Next if the welcome page appears.
- Select database
- Click Next again
- Select your database triggers, stored procedures, tables, UDFs and views, then click Next
- On the next few pages, select all objects of the selected type with Select All, then click Next
- Select "Script for File" and save the file where you can find it.
- Click Finish
After the script is created, you can use your favorite script search tools. You can even use regular expressions in SSMS with the Find Files command.
source to share
Another option (if it suits 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 and also rewrite sProc sp to use either different, depending on which one is passed ... Then the old code that calls it with the old parameter will continue to function as it was used for ...
source to share