Finding SQL-syntaxes

• gkramer 23701

  Ten Centuries

  Points: 1381

  More actions

  December 9, 2014 at 2:17 am

  #313652

  Gents,

  Is there a simple (or more complex) way to search all scripts (SP/Jobs) in search for a specific SQL-syntax?

  Or is there a way to track it down comming from an extern datasource?

  I want to know which script contains "execute xp_cmdshell" - but this goes for more syntaxes I'm searching for within all databases registered on an Instance.

  Can someone shad a light on this?

  Regards,

  Guus Kramer

  The Netherlands

• Gazareth

  One Orange Chip

  Points: 27737

  More actions

  December 9, 2014 at 2:51 am

  #1763450

  For Procs/Views/Triggers/Functions:

  select * from sys.sql_modules where definition like '%xp_cmdshell%'

  This runs in the context of the current database; you'll need to use sp_msforeachdb or your own loop/cursor to go through multiple databases.

  For T-SQL in jobs:

  select * from msdb.dbo.sysjobsteps where command like '%xp_cmdshell%'

• gkramer 23701

  Ten Centuries

  Points: 1381

  More actions

  December 9, 2014 at 5:26 am

  #1763481

  Thanks SSCrazy,

  this works fine for me... I'll loop it with sp_foreachedb to make a general script to check the whole instance ....

  Guus

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  December 9, 2014 at 7:18 am

  #1763514

  Before you use the sp_foreach proc you should take a look at this article. http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/