A little over 2 years ago I posted a couple of methods of finding the instances of a string within stored procedures - at the time I'd left some debug 'Print @' code in at least one stored procedure and needed to find it before releasing the code.

Today, I wanted to do the same thing, and as over the past 2 years I've been weaning myself off accessing the sysobjects tables, instead making use of the INFORMATION_SCHEMA views, I decided it was time to add an update to that earlier post.

So, using INFORMATION_SCHEMA.ROUTINES it is coded as :
  SELECT SPECIFIC_NAME
  FROM INFORMATION_SCHEMA.ROUTINES
  WHERE ROUTINE_DEFINITION LIKE '%PRINT @%'