Link to home
Create AccountLog in
Avatar of crazywolf2010
crazywolf2010Flag for United Kingdom of Great Britain and Northern Ireland

asked on

Tracing text string within TSQL procedure code

Hi,
I am taking over some TSQL code. The code is written by 3rd party and there isn't much documents to describe what is done.

I am receiving email alerts and I need to locate which procedure is sending them. They are not using database mailer which I can trace within SSMS. The coder used another 3rd party component to send emails using xp_cmdshell.

If I have an alert like this
Some files are not processed
Customer ID: 101
Start Time: 2012-11-05 12:00:01
End Time: 2012-11-06 12:00:01
Expected number of processed entries

Open in new window


How can I look within TSQL code for these strings? The componets next to ":" are variables and will change.

Thanks
Avatar of Christopher Gordon
Christopher Gordon
Flag of United States of America image

When I'm doing a T-SQL search, I usually just script out all of the views, procedures, and functions to a new window.


Right Click Database...
Tasks...
Generate Scripts...

(Use All of the Defaults)

Script to New Query Window.

Then I just do a "FIND" in the new query window for desired text.  In your case, maybe just look for "Expected number of processed" when everything is scripted out.

This is assuming though that this code is in a procedure, view, or function.
Avatar of crazywolf2010

ASKER

Hi,
That is lot of work specially when I have 50 databases on single instance and many procs,triggers,functions. There must be a way to locate this detail using DMV.

Thanks
If they are using Stored procedures, you can use
SELECT OBJECT_NAME(ID)
FROM sys.syscomments
WHERE Text like '%<YourInputTextHere>%'

Alternative method is to run sql server profiler trace to capture the statements or stored procedure executing in the server.
Ha, yeah, that is too much work.  I made the assumption that it was one database.  Yeah, I have some code that does a look up that uses sys.objects and something else.  Give me a second to find it.
declare @searchstring nvarchar(500) = 'patient'

select
      obs.name                  as      SQL_Object_Name
,      modules.definition      as      SQL_Object_Code

from sys.sql_modules modules
 
inner join sys.objects obs on
      modules.object_id = obs.object_id

where modules.definition like '%'+ @searchstring +'%'
Oh, and patient would need to be replaced by your search string.   I forgot to remove that.
Avatar of Jared_S
Jared_S

There several ways that it could be coded. If you're using SQL Server 2008  you could look for the last time run time of stored procedures on the database, and then compare that to the time the messages go out.
Hi,
Many Thanks, This seems working but this will only trace one database I am connected to.

I will need this to go recursive against all DB on my system and locate the DB, proc string is located at. Something using exec sp_MSforeachdb USE [?];

Thanks
Yep, you could use sp_MSforeachdb for this.  You'll just need to put that code into a variable like this.


declare @searchstring nvarchar(500) = 'patient'

declare @myDynamicSearchString nvarchar(max)
 
select @myDynamicSearchString =

'select
      obs.name                  as      SQL_Object_Name
,      modules.definition      as      SQL_Object_Code

from sys.sql_modules modules
 
inner join sys.objects obs on
      modules.object_id = obs.object_id

where modules.definition like ''%'  + @searchstring + '%'''  

select @myDynamicSearchString

EXEC sp_MSforeachdb @myDynamicSearchString
Hi,
I tried your code and it ran but didn't return anything.

This code snippet worked for me. Now the issue is, to identify which DB has this procedure as output doesn't return DB name.

use master
go
exec sp_MSforeachdb 'USE [?]; 
select
      obs.name                  as      SQL_Object_Name
,      modules.definition      as      SQL_Object_Code
from sys.sql_modules modules
inner join sys.objects obs on
      modules.object_id = obs.object_id
where modules.definition like ''%Some log files were %'''

Open in new window

You can add db_name() into the select statement

use master
go
exec sp_MSforeachdb 'USE [?];
select
      obs.name                  as      SQL_Object_Name
,      modules.definition      as      SQL_Object_Code
, dbname() as SQL_Database_Name
from sys.sql_modules modules
inner join sys.objects obs on
      modules.object_id = obs.object_id
where modules.definition like ''%Some log files were %'''
Avatar of Anthony Perkins
I would strongly suggest you download and install the free app SQL Search from Red-Gate.  See here:
http://www.red-gate.com/products/sql-development/sql-search/
Hi gohord,
I have an error
Msg 195, Level 15, State 10, Line 5
'dbname' is not a recognized built-in function name.

I then tried SELECT  ''?'' as dbname but similar error reported. I am on SQL2008R2.

Thanks
*slap*

My bad.  It's db_name().  Not dbname().  I mistyped.
Thanks Buddy.
This code works a treat for me.

use master
go
exec sp_MSforeachdb 'USE [?];
select
      obs.name                  as      SQL_Object_Name
,      modules.definition      as      SQL_Object_Code
, db_name() as SQL_Database_Name
from sys.sql_modules modules
inner join sys.objects obs on
      modules.object_id = obs.object_id
where modules.definition like ''%copy file failure%''' 

Open in new window


Unfortunately after"copy file failure" -> We have a variable which I need to look at to differentiate between number of objects with same data.

Anyway to display the line containing string in return results than the  entire procedure code?

Thanks
This *might* help you get started.  I'll look to see if there is a way to detect a line break in T-SQL.  Let me know if this does the trick though.

declare @searchstring varchar(1200) = 'yoursearchcodehere'

declare @searchSQL nvarchar(max) =
'USE [?];
select
    obs.name                  as      SQL_Object_Name
,   modules.definition       as      SQL_Object_Code
,   db_name() as SQL_Database_Name
,      substring(modules.definition, charindex(''' + @searchString + ''',  modules.definition), 100) as Code_Location
from sys.sql_modules modules
inner join sys.objects obs on
      modules.object_id = obs.object_id
where modules.definition like ''%' + @searchstring + '%'''

exec sp_MSforeachdb  @searchSQL
Hi gohord,
I can see some results as earlier but still not the line where this code could be located.

Thanks
The column "Code Location" should take you to the area in your code where your search string exists.  You're not seeing the value in there anywhere?
I did precisely that. Copied code into notepad and looked for string I am looking for but "no results were there"

Thanks
ASKER CERTIFIED SOLUTION
Avatar of Christopher Gordon
Christopher Gordon
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account