advanced code snippet search

Posted By

hairajeshk on 11/09/10


Tagged


Versions (?)

Find sp or table name in all stored procs

 / Published in: SQL
 

URL: http://blog.sqlauthority.com/2006/12/10/sql-server-find-stored-procedure-related-to-table-in-database-search-in-all-stored-procedure/

Expand | Embed | Plain Text 
  1. ----Option 1
  2. SELECT DISTINCT so.name
  3. FROM syscomments sc
  4. INNER JOIN sysobjects so ON sc.id=so.id
  5. WHERE sc.TEXT LIKE '%tablename%'
  6. ----Option 2
  7. SELECT DISTINCT o.name, o.xtype
  8. FROM syscomments c
  9. INNER JOIN sysobjects o ON c.id=o.id
  10. WHERE c.TEXT LIKE '%tablename%'

Report this snippet  

Comments

RSS Icon Subscribe to comments
Posted By: hairajeshk on December 8, 2010

How can I find if particular table is being used in the stored procedure? How to search in stored procedures? --Searching for Empoloyee table SELECT Name FROM sys.procedures WHERE OBJECTDEFINITION(OBJECTID) LIKE '%Employee%' GO --Searching for Empoloyee table and RateChangeDate column together SELECT Name FROM sys.procedures WHERE OBJECTDEFINITION(OBJECTID) LIKE '%Employee%' AND OBJECTDEFINITION(OBJECTID) LIKE '%RateChangeDate%' GO

You need to login to post a comment.