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/
|