How to find a query under a particular stored procedure in SQL Server

I’ve got a question this morning about how to find a particular query in a huge list of stored procedure. It’s not that hard, what you need to do is to use SYS object and sys.procedures basically contains all the stored procedure under the active database that you are using. This is the query that you can use to find a keyword product in any stored procedure

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_DEFINITION LIKE '%product%'
    AND ROUTINE_TYPE='PROCEDURE'

And the query below is used to find the stored procedure name that match with what you are looking for

SELECT *
FROM sys.procedures where OBJECT_DEFINITION(object_id) LIKE '%product%'

UPDATE:
How to find all the triggers on your Database

SELECT tbl1.[name] TableName, tbl0.[name] TriggerName,
CASE
WHEN tbl1.deltrig = tbl0.id  THEN 'OnDelete'
WHEN tbl1.instrig = tbl0.id THEN 'OnInsert'
WHEN tbl1.updtrig = tbl0.id THEN 'OnUpdate'
END 'Operation Type', tbl1.*,tbl0.*
FROM sysobjects tbl0 JOIN sysobjects tbl1 ON tbl0.parent_obj = tbl1.[id] WHERE tbl0.xtype='TR'

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s