Deleting a stored procedure in SQL server

Question

Before I delete/replace a stored procedure, I would usually do a string search of the name of the stored procedure in the working project to make sure no unsuspecting code is using it. Is there a better and more reliable way of doing this?

Oh and the code is in c# and i'm using vs2008

Answer 1

If you have a comprehensive suite of unit tests, delete the SP and run through the tests to see if anything breaks. If you don't have a suite, there's no time like the present to start writing it.

Answer 2

I have this code for searching text on all the triggers (a bit harsh, sorry), maybe u can adapt it for searching on other database objects also:

SELECT Tables.Name TableName,
  Triggers.name TriggerName,
  Triggers.crdate TriggerCreatedDate,
  Comments.Text TriggerText
FROM sysobjects Triggers
 Join sysobjects Tables On Triggers.parent_obj = Tables.id
 Join syscomments Comments On Triggers.id = Comments.id
WHERE
 Triggers.xtype = 'TR'
AND Tables.xtype = 'U'
AND (@ttable_name = '' OR Tables.name = @table_name)
AND Comments.Text LIKE '%' + @search + '%'
ORDER BY Tables.Name, Triggers.name

Answer 3

You should search before you drop it, by using the sys.sql_dependencies catalog view:

select object_name(object_id), *
    from sys.sql_dependencies
    where referenced_major_id = object_id('<nameofproceduretobedropped>');

This catches all references to the procedure in other procedures, but it cannot catch dynamic sql dependencies.

With stored procedures though most dependencies are external ie. in the client code.

Answer 4

Ideally, in your C# code, you only have one method that is (conceptually) "allowed" to call the stored procedure, and that method must live in the data access layer (the only layer allowed to have access to the database connection string).

The ability to actually enforce anything like this at the C# level is pretty lacking. It generally has to be enforced through coding conventions and code review.

At this point, doing a string search through the C# code is probably your only option and I agree it's not a great one. If you can limit your string search to a single C# project, that's great, but not necessarily likely in the wild.

Unit tests are also a decent technique to mitigate some of the risk around situations like this.

Answer 5

or rename the sp instead of deleting it. if there's an unidentified dependency you can quickly get your sp back.

another thing you can do is script out your views, functions, and sps, and search the scripts. again, won't catch dynamic sql, but might catch a few extra references.

Answer 6

For SQL Server 2005 and above, works with longer code.

SELECT
    OBJECT_NAME(object_id)
FROM
    sys.sql_modules
WHERE
    definition LIKE '%' + 'mySP' + '%'