CODEWITHSUNDEEP
c#sql-serverstored-proceduresvisual-studio-2008
burnt1ce
burnt1ce

Reputation: 14897

Deleting a stored procedure in SQL server

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?

Upvotes: 1

Views: 973

Answers (6)

Beth
Beth

Reputation: 9607

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.

Upvotes: 3

gbn
gbn

Reputation: 432210

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

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

Upvotes: 5

Michael Maddox
Michael Maddox

Reputation: 12489

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.

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294217

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.

Upvotes: 1

Jhonny D. Cano -Leftware-
Jhonny D. Cano -Leftware-

Reputation: 18013

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

Upvotes: 1

TALlama
TALlama

Reputation: 16407

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.

Upvotes: 2

Related Questions