Find procedures in which CTE is used

• Megha P

  SSCommitted

  Points: 1976

  More actions

  September 22, 2014 at 11:54 pm

  #296816

  Hi,

  I want to find the stored procedures where cte is used, as error log is full with incorrect syntax near 'with'.

  we have multiple procedures where WITH (NOLOCK) OR WITH(NOLOCK) is used, i got all those procedure with this query.

  select OBJECT_NAME(id)name,* from sys.syscomments where text like '%With%[^ (nolock)]'

  how can i get only those procedures where only 'with' is used?

  Thanks..

• Johan Bijnens

  SSC Guru

  Points: 135258

  More actions

  September 23, 2014 at 12:44 am

  #1747297

  IMO that must be something that comes from either:

  - someone executing DDL for implementation of stuff

  or

  - a dynamic sql statement someone is executing.

  SQLserver will not let you implement DDL having syntax errors, so you'll not find these in sys.syscomments !

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me