Check is stored proc is call - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Check is stored proc is call

Author

Topic

ryanlcs
Yak Posting Veteran

62 Posts

Posted - 2009-08-13 : 08:43:18

Hi..

I have a lot of stored proc, among them some called the xp_sendmail function.

So what I want to find out is there a stored proc to check that inside which stored proc that the xp_sendmail is call?

I've seem 1 script before, it can be done, just lost it.

Thank You.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-13 : 08:49:55

select * from sys.sql_modules
where definition like '%xp[_]sendmail%'

N 56°04'39.26"
E 12°55'05.63"

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-08-13 : 09:09:04

Hi Peso

SELECT * FROM SYS.SYSCOMMENTS WHERE TEXT LIKE '%XP[_]SENDMAIL%'

so What is the difference for both..

-------------------------
R..
http://code.msdn.microsoft.com/SQLExamples/
http://msdn.microsoft.com/hi-in/library/bb500155(en-us).aspx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-13 : 09:18:44

It would be the 4000 character limit of NVARCHAR in sys.syscomments...

N 56°04'39.26"
E 12°55'05.63"

ryanlcs
Yak Posting Veteran

62 Posts

Posted - 2009-08-13 : 11:05:05

hm...dont really return all the stored proc...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-13 : 12:12:15

I'll help you further...

select object_Name(object_id) from sys.sql_modules
where definition like '%xp[_]sendmail%'

N 56°04'39.26"
E 12°55'05.63"

ryanlcs
Yak Posting Veteran

62 Posts

Posted - 2009-08-14 : 09:31:46

sorry to said your method didnt work out as i expected. No stored proc is return.

I've seem one that uses a system stored proc to achive that.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-14 : 10:15:52

sp_findtext ?

do so sp_helptext 'sp_findtext' to see what sp_findtext does internally :-)

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-14 : 10:18:38

Are you currently in the correct database running the code?

Run this complete set of code and post back your result.

CREATE PROCEDURE Peso
AS
EXEC xp_sendmail
GO

select object_Name(object_id) from sys.sql_modules
where definition like '%xp[_]sendmail%'

GO
DROP PROCEDURE Peso

Your result should be "Peso".

N 56°04'39.26"
E 12°55'05.63"

ryanlcs
Yak Posting Veteran

62 Posts

Posted - 2009-08-16 : 03:45:38

i think i must made a mistake somewhere...your script actually works.
Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-16 : 04:21:26

You're welcome.

N 56°04'39.26"
E 12°55'05.63"