datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to find all stored procedures where column is used?

Execute the following Microsoft SQL Server T-SQL example scripts in Management Studio Query Editor search stored procedure definitions for column usage.


-- SQL Server object_definition - SQL select into temporary table
-- SQL information_schema views - ANSI-92 SQL standard
USE   AdventureWorks;
 
SELECT SPROC = specific_schema + '.' + specific_name,
       [Text] = OBJECT_DEFINITION(OBJECT_ID(specific_schema + '.' + specific_name))
INTO   #SprocDefs
FROM   INFORMATION_SCHEMA.ROUTINES
WHERE  routine_type = 'PROCEDURE'
GO
 
-- SQL search stored procedure definitions for column usage
-- SQL like comparison operator for wildcard search
SELECT SPROC
FROM   #SprocDefs
WHERE  [Text] LIKE '%ListPrice%'
ORDER BY SPROC
GO
/* Results
 
SPROC
dbo.sprocGetWhereProductUsed
dbo.uspGetBillOfMaterials
dbo.uspGetWhereUsedProductID
dbo.uspProductsByColor
Production.sprocSearchProductList

*/

------------
-- List stored procedures where column is used
-- SQL query for sproc list using ListPrice column
-- SQL Server system tables - SQL inner join
USE AdventureWorks;
 
SELECT   DISTINCT StoredProcedure = o.name
FROM     SYS.OBJECTS o
         INNER JOIN SYS.SYSCOMMENTS c
           ON o.object_id = c.id
              AND o.TYPE = 'P'
              AND c.TEXT LIKE '%ListPrice%'
ORDER BY StoredProcedure
 
GO
 
/* Results
 
StoredProcedure
sprocGetWhereProductUsed
sprocSearchProductList
uspGetBillOfMaterials
uspGetWhereUsedProductID
uspProductsByColor
*/

 

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

 
-- Alternate query using INFORMATION_SCHEMA views
-- NOTE: ROUTINE_DEFINITION is limited to 4000 characters
USE AdventureWorks;
 
SELECT   DISTINCT StoredProcedure = specific_name
FROM     INFORMATION_SCHEMA.ROUTINES
WHERE    routine_definition LIKE '%ListPrice%'
         AND routine_type = 'PROCEDURE'
-- AND routine_type = 'FUNCTION'
ORDER BY StoredProcedure
         /* Results
 
StoredProcedure
sprocGetWhereProductUsed
sprocSearchProductList
uspGetBillOfMaterials
uspGetWhereUsedProductID
uspProductsByColor

*/

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

Exam Prep 70-461
Exam 70-461