Database Simplified Headline Animator

Database Simplified

Monday, 7 November 2011

How to find list of stored procedure with content info in SQL Server

This post describes the how can you find list of stored procedure with specific text content or how to answer following question.

  • List All Stored Procedure, Function, Views, Triggers which uses a Update or Insert or Delete Statement.
  • List All Stored Procedure, Function, Views, Triggers which uses temporary table.
  • List All Stored Procedure, Function, Views, Triggers which uses a particular table.
  • and Many More.

There is a system table which contains information about stored procedure,view and trigger contents “syscomments

List All Stored Procedure, Functions, Views, Triggers which uses “Update, Insert or Delete Statement



Select Distinct A.Name,A.type_desc
from SysComments B
Join sys.all_objects A On B.id=A.object_id
where text Like '%update %' or text like '%insert %' or text like '%delete %'

List All Stored Procedure, Functions, Views, Triggers which uses temporary table.



Select Distinct A.Name,A.type_desc
from SysComments B
Join sys.all_objects A On B.id=A.object_id
where Replace(Text,' ','') Like '%Table#%'

List All Stored Procedure, Functions, Views, Triggers which uses a particular table.



Select Distinct A.Name,A.type_desc
from SysComments B
Join sys.all_objects A On B.id=A.object_id
where text like '%Sys.Objects%'

List All Stored Procedure , Functions, Views, Triggers which contains a specific text



Select Distinct A.Name,A.type_desc
from SysComments B
Join sys.all_objects A On B.id=A.object_id
where text like '%Data%'

So “syscomments” is really helpful.

NOTE: Searching cannot be done when stored procedure function or View Are Encrypted.

2 comments:

  1. create procedure dbo.proc_SearchAnyThingInModule(@searchVal varchar(200))
    as
    SELECT
    o.type_desc AS ROUTINE_TYPE,
    o.[name] AS ROUTINE_NAME,
    m.definition AS ROUTINE_DEFINITION
    FROM
    sys.sql_modules AS m
    INNER JOIN sys.objects AS o ON m.object_id = o.object_id
    WHERE
    m.definition LIKE '%'+@searchVal+'%'
    go

    --proc_SearchAnyThingInModule '%any thing%'

    ReplyDelete