ansaurus

Question

How can I programmatically determine if a stored procedure selects from another database?

Answer 1

+3 A:

The following is a stored proc that will search for text contained in any stored proc. You could use this to search for the various databases that might get called.

CREATE PROCEDURE FindTextInSP
@StringToSearch varchar(100) 
AS 
   SET @StringToSearch = '%' +@StringToSearch + '%'
   SELECT Distinct SO.Name
   FROM sysobjects SO (NOLOCK)
   INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
   AND SO.Type = 'P'
   AND SC.Text LIKE @stringtosearch
   ORDER BY SO.Name
GO

Randy Minder 2010-02-17 19:52:22

Thanks. Works like a charm. And if I replace 'P' with 'V' that should work for views?

Jonathon Watney 2010-02-17 20:25:13

This will find any sprocs where "TargetDB" is _explicitly_ referenced, but it won't find cases where a sproc dynamically determines a DB name (perhaps by calling some query) and then executes dynamic SQL using spexec(). That's a nit pick though, and I'm not sure _any_ solution exists that will do that...

Seth Petry-Johnson 2010-02-17 20:27:12

That's either EXEC() or sp_ExecuteSQL. Never came across spexec(). :-)

Frank Kalis 2010-02-17 20:35:57

@Seth Petry-Johnson, Okay, but it's an edge case I'm willing to ignore for the moment. :) But if I had to find which procedures were executing dynamic SQL would it be enough to include '%spexec%' as @StringToSearch?

Jonathon Watney 2010-02-17 20:40:32

No, you won't find sp_executeSQL because of the missing underscore. Either run the procedure twice with different arguments or add a second (third....) parameter to the procedure to make it look like something like this:SELECT Distinct SO.NameFROM sysobjects SO (NOLOCK)INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.IDWHERE SO.Type = 'P' AND SC.Text LIKE @stringtosearch OR SC.Text LIKE @stringtosearch2ORDER BY SO.NameEDIT: Sorry, can't figure out how to format the code better...

Frank Kalis 2010-02-17 20:49:20

@Frank Kalis: er, yeah, that's what I meant. Too many threads going at once...

Seth Petry-Johnson 2010-02-17 21:16:58

Great. Thanks for the help guys.

Jonathon Watney 2010-02-17 22:13:14

Answer 2

A:

Would this code do...You would need to temporarily connect to the database on the other server? You would need to be a member of the 'sysadmin' group for this to work...top off my head....it may be wrong, feel free to point this out...

USE MASTER
GO

sp_addserver @server='AnotherSQLServer'
USE [AnotherSQLServer].Master
GO

IF EXISTS (SELECT name FROM [AnotherSQLServer].sysobjects WHERE name = 'some_proc' AND type = 'P') 
THEN
    ' DO WHAT YOU HAVE TO DO
    'DROP PROCEDURE some_proc
END
GO

USE MASTER
GO

sp_dropserver @server='AnotherSQLServer'

Hope this helps, Best regards, Tom.

tommieb75 2010-02-17 19:59:23

I don't need to access a database on a different server, but thanks.

Jonathon Watney 2010-02-17 20:26:03

@Jonathon: I misunderstood when you said "pull data from databases other than the one it's stored in" and "find which procedures do that sort of thing without eyeballing each one", in other words, I thought you meant to find out the sprocs on another database residing on another server...but no problem! Glad to help you anyway! :)

tommieb75 2010-02-17 20:30:57

@tommieb75, That's cool. :) Mentioning that everything is on the same server had crossed my mind. I've clarified the question now.

Jonathon Watney 2010-02-17 20:44:38

ansaurus

tags:

views:

answers:

How can I programmatically determine if a stored procedure selects from another database?

related questions