Use SQL statements to find stored procedures, triggers, functions, etc. that contain a keyword (MS SQL SERVER only)

Source: Use SQL statements to find stored procedures, triggers, functions, etc. that contain a keyword (MS SQL SERVER only)

The first approach: querying using system tables

--replace text with the one you want to find. Select name from sysobjects o, syscomments s where o.id = S.id and the text like '%text% ' and O. xtype= ' P ' xtype: Object type. Can be one of the following types of objects: C = CHECK Constraint D = default value or defaults constraint f = FOREIGN key constraint L = log fn = scalar function if = inline table function p = Stored procedure pk = PRIMARY KEY constraint (type is K RF = Copy Filter stored procedure s = system table TF = Table function TR = trigger u = User table UQ = UNIQUE constraint (type K) V = view x = Extended stored Procedure

Second method: Query with System read-only view

--replace text with the content you want to find SELECT Routine_name, routine_definition from information_schema. ROUTINES --Querying stored procedures and functionsWHERE routine_definition like '%text% ' and routine_type= ' PROCEDURE ' select * FROM information_schema. PARAMETERS--Querying the parameters of stored procedures and functions SELECT * FROM information_schema. TABLES--Querying the database for tables and views

Use SQL statements to find stored procedures, triggers, functions, etc. that contain a keyword (MS SQL SERVER only)