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.

3 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
  2. In some circumstances the punch and die "nest" collectively to create a melancholy in the stock. In progressive stamping, a coil of stock is fed into an extended die/punch set with many stages. Multiple simple formed holes additionally be} produced in a single stage, but complicated holes Shower Caps are created in a number of} stages.

    ReplyDelete
  3. Contact Northwood Tech for details about the expert occupations and industries in Northwest Wisconsin and advice in regards to the necessities and application procedures. Northwood Tech works closely with regional apprenticeship places of work , enterprise and industry, and native labor union places of work. Jaycon Systems specializes in bringing hardware products to life by offering an entire service line that takes product concepts to mass production. Injection molding parameters are not the only way to rectify the molding course of. Constraints like dimensional inaccuracies, warpage, sink marks, and shrinkage may be taken care of partly best straw lifeguard hat with the assistance of mold design.

    ReplyDelete