Execute the following Microsoft SQL Server T-SQL queries to demonstrate searching all stored procedures for keyword:

USE AdventureWorks2008;

GO

SELECT SCHEMA_NAME(schema_id) as SchemaName, OBJECT_NAME(sm.object_id) AS ObjectName,

       o.type as Type, o.type_desc as TypeDesc, sm.definition as [Definition]

FROM sys.sql_modules AS sm

  JOIN sys.objects AS o ON sm.object_id = o.object_id

WHERE type='P'

  AND definition like '%Production%'

ORDER BY SchemaName, ObjectName;

GO

------------

USE AdventureWorks2008;

SELECT Name,

       SPROCDefinition=left(TEXT,1024)

FROM   sysobjects

       JOIN syscomments

         ON sysobjects.id = syscomments.id

WHERE  TYPE = 'P'

       AND TEXT LIKE '%Production%'

/*

Name                                SPROCDefinition

uspGetWhereUsedProductID            CREATE PROCEDURE

uspGetBillOfMaterials               CREATE PROCEDURE

*/

------------

SELECT * FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_DEFINITION LIKE '%Production%'

      AND ROUTINE_TYPE='PROCEDURE'

/* Partial Results

SPECIFIC_CATALOG  SPECIFIC_SCHEMA   SPECIFIC_NAME

AdventureWorks2008      dbo   uspGetWhereUsedProductID

AdventureWorks2008      dbo   uspGetBillOfMaterials

*/

------------

Related article:

http://www.sqlusa.com/bestpractices2008/object-dependency/