Programmabilities Blog

 
Search Stored Procedures
This searches all the Stored Procedures in 1 SQL Server database for the said string. I tested it in Query Analyzer and it found all the Stored Procedures that had occurrences of my "lq_Campaign" in 1 database:

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

Here is another way

Below query you can searcg any table, stored procedure or views what ever has that expression as a column or object name.

SELECT * FROM SYSOBJECTS WHERE ID IN (SELECT ID FROM SYSCOMMENTS WHERE TEXT LIKE ‘%PRODUC%’)

The query produced all the object names--where ever it finds expression like ‘PRODUC‘.

Labels: sql

- posted by programmabilities @ 15:06