Click here to Skip to main content
Click here to Skip to main content
Alternative Tip/Trick

Tagged as

Find particular word or text from the entire stored procedure

, 26 Jan 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
since syscomments is nvarchar(4000) the word may be split into several rows.. so you concat them.. or at least pair them up... here is the sp I use to search on the stored proceduresCREATE PROCEDURE [dbo].[sp_FindInProc] @sText NVARCHAR(MAX) = '%'ASBEGIN SET NOCOUNT ON ...
since syscomments is nvarchar(4000) the word may be split into several rows.. so you concat them.. or at least pair them up... here is the sp I use to search on the stored procedures
 
CREATE PROCEDURE [dbo].[sp_FindInProc]
    @sText NVARCHAR(MAX) = '%'
AS
BEGIN
 
    SET NOCOUNT ON
    
SELECT DISTINCT obj.id as ID,obj.name AS Name
FROM
(
            SELECT  a.id,CAST(COALESCE(a.text,'') AS NVARCHAR(MAX)) + CAST(COALESCE(b.text,'')  AS NVARCHAR(MAX)) AS [text]
            FROM    syscomments a LEFT JOIN 
                    syscomments b ON a.colid+1 = b.colid and a.id = b.id
) AS comment
INNER JOIN sysObjects obj ON obj.id = comment.id
WHERE obj.type = 'P' AND comment.text like @sText
ORDER BY obj.Name            
 
    SET NOCOUNT OFF
 
END

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

CaldasGSM
Software Developer
Portugal Portugal
From the age of 15 I started programing in QBasic at home after several jobs I realized that what I wanted was to work with computers, so I joined a IT company, originally as a web designer, but thanks to my self learning abilities I made my way into the programmers team. Today I know VB, VBScript, Javascript, HTML, SQL , C# and C++... even if I never went to college.. Smile | :)

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141216.1 | Last Updated 26 Jan 2012
Article Copyright 2012 by CaldasGSM
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid