Wednesday 3 July 2013

Query to find text in a SP

I get request couple of times to find the SP which uses some text(may be object or  author name or modified by name or comments)..

you can take help of below queries to get the list of SP's that are using text..

Query1:

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

--Replace the Any_Text with text you want and search..
or

-- Open query is the text or word we are finding in the below query.
--Below query is ised to find the text in SP

Query 2:
SELECT sys.sysobjects.name, sys.syscomments.text

FROM sys.sysobjects INNER JOIN syscomments

ON sys.sysobjects.id = sys.syscomments.id

WHERE sys.syscomments.text LIKE '%OpenQuery%'

AND sys.sysobjects.type = 'P'

ORDER BY sys.sysobjects.NAME

No comments:

Post a Comment