-1

I have a database that contains more than thousand stored procedures. I need to list the stored procedures that contain more than seven join operations only within a select statement (a select statement that contains more than seven join operations) for optimization purpose. Is there any way to do this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
santosh timalsina
  • 197
  • 9
  • I have asked but this is not exactly the same question.I just need the list of Sps that contains more than seven join operations within a select statement,not count all the join keyboards within all text of Sps. – santosh timalsina Jul 23 '13 at 08:35
  • This should get you on the right track: http://stackoverflow.com/questions/5079457/how-do-i-find-a-stored-procedure-containing-text – Alexander Jul 23 '13 at 08:40

2 Answers2

0

you can use: system table syscomments where is stored stored procedure text, trigger and so on (in text field)

SELECT object_name(id), * FROM syscomments

In this way you can implement a query about number of JOIN keyword in a text (using i.e. PATHINDEX function or other functions about string quering)

object_name(id) return the name of your object, in your case the sp name.

Joe Taras
  • 15,166
  • 7
  • 42
  • 55
0

if you really need it - you can use TSql100Parser class ( http://msdn.microsoft.com/en-us/library/microsoft.data.schema.scriptdom.sql.tsql100parser%28v=vs.100%29.aspx )

It is not so hard to walk through the parsed tree and find all code cases you need.

But not so easy, of course.

Locky
  • 132
  • 4