Posted By

axs221 on 12/14/10


Tagged

sql search stored server procedures keywords


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

wilson0825


Search SQL Server field names and stored procedure text


 / Published in: SQL
 

Search SQL Server field names and stored procedure text, based on a keyword.

  1. -- Search Table Field Names
  2. SELECT a.name, b.name
  3. FROM syscolumns a
  4. INNER JOIN sysobjects b ON a.id = b.id
  5. WHERE LEFT(b.name,3) IN ('tbl','sys')
  6. AND a.name LIKE '%KEYWORD%'
  7. ORDER BY b.name
  8.  
  9.  
  10. -- Search Stored Procedure Text
  11. SELECT sys.sysobjects.name, sys.syscomments.text
  12. FROM sys.sysobjects INNER JOIN syscomments
  13. ON sys.sysobjects.id = sys.syscomments.id
  14. WHERE sys.syscomments.text LIKE '%KEYWORD%'
  15. AND sys.sysobjects.type = 'P'
  16. ORDER BY sys.sysobjects.NAME
  17.  
  18.  
  19. -- OR this alternative:
  20. SELECT ROUTINE_NAME, ROUTINE_DEFINITION
  21. FROM INFORMATION_SCHEMA.ROUTINES
  22. WHERE ROUTINE_DEFINITION LIKE '%prSendCdoSysMail%'
  23. AND ROUTINE_TYPE='PROCEDURE'
  24. ORDER BY ROUTINE_NAME

Report this snippet  

You need to login to post a comment.