Need to find a store procedure in a database

Question

Need to find a store procedure in a database

montserrat.deza

Right there with Babe

Points: 725
More actions
August 20, 2017 at 7:01 pm

#339455

Hello Experts.
Ive been trying to find the whole weekend a logic which is creating a xml file of this calculation:
But no lock. This database is huge!!
so Ive been reading a lot of store procedures but no lock.
I run this query also:
select * from sysobjects
where name like '%Dutiable%
but is not working.
Please help Im super frustrated now due a due date I need to accomplish presenting this logic and I did not have any luck yet.
Thank you so much

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Andrew P SSCarpal Tunnel Points: 4649 More actions · Answer 1

I find the free add-on ApexSQL Search useful for finding procedures (or data) in database(s), but you could use the below query as a starter to search stored procedure definitions.

select objects.name, sql_modules.definition from sys.sql_modules inner join sys.objects on sql_modules.object_id = objects.object_id where sql_modules.definition like '%Dutiable%'
Andrew P.

montserrat.deza Right there with Babe Points: 725 More actions · Answer 2

It just find one sp I already saw but is not the one Im looking for.

So I cand find the for Dutiable and is not the one has the logic

And to be more specific I am running it by the next value: Declared Values and is not there...

:((

Any ideas?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Searching for the XML tags won't get you anywhere, because those get generated from data, they're not hard coded into the procedure. Instead, look for procedures that are generating XML from resultsets.
Search sys.sql_modules for '%FOR XML%'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

montserrat.deza Right there with Babe Points: 725 More actions · Answer 4

Stilll no lock,

Is there a way to find by an specific xml tag?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Yes, but since the XML tags are generated from data, you need to search for a query that runs against whatever table has those columns in, and has the FOR XML query option, that's how XML is generated in T-SQL.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Avi1 SSCrazy Points: 2323 More actions · Answer 6

Did you ran that script in all DBs? your proc may in different DB .
you can search in SQL jobs

select * from msdb..sysjobs j
inner join msdb..sysjobsteps s
on j.job_id = s.job_id
and s.command like '%XML%'

montserrat.deza Right there with Babe Points: 725 More actions · Answer 7

thank you guys, I really appreciate the help. I finally found the sp creating the file. The one that was hiding it was a dll that is why I wasn't able to see it but I could troubleshoot fine the sp with these querys...thank uuuu