Advanced Search

Search SQL Server field names and stored procedure text

/ Published in: SQL
Save to your folder(s)
Search SQL Server field names and stored procedure text, based on a keyword.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  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


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.