Searching in stored procs code
-
Hi all!
Is it possible to search for a string inside all stored proc source codes? If it is, could you give me a hint. E. g. How do you display the stored proc code?
Thanks!
/Tomi
-
The code for stored procedures is stored in the syscomments system table, the following script should do the trick:
--USE YourDatabase
--GO
SELECT
o.name, c.text
FROM
syscomments c
INNER JOIN sysobjects o ON c.id = o.id
WHERE
xtype = 'P'
AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0
AND c.text LIKE '%SearchString%'
GO
-
hey!
Thanks! It worked!
/Tomi
-
A bit late but here's the same I picked up somewhere that was wrapped in a stored proc ...
/* CREATE PROCEDURE sp_FindStringInCode
-- Input variables, default null for custom error output.
@find VARCHAR(50) = NULL,
@type CHAR(2) = NULL
AS
*/
declare
@find VARCHAR(50),
@type CHAR(2)
SET@find = 'objectname'
SET@type = 'objecttype'
/* Check for null or invalid input and show custom error. */
IF @find IS NULL AND @type IS NULL
BEGIN
RAISERROR ('This procedure has two required parameters @find and @type',16,-1)
RETURN
END
ELSE IF @find IS NULL
BEGIN
RAISERROR ('You must enter a valid like criteria for @find without the leading/ending % wildcard.',16,-1)
RETURN
END
ELSE IF @type IS NULL OR @type NOT IN ('C','D','FN','P','TR','V')
BEGIN
RAISERROR('No value was entered for @type.
Valid values for @type are
C = Check Constraint
D = Default
FN = Function
P = Procedure
TR = Trigger
V = View',16,-1)
RETURN
END
/* Set wildcards on end of find value. */
SET @find = '%' + @find + '%'
/* Output object names which contain find value. */
SELECT OBJECT_NAME([id]) FROM syscomments
WHERE [id] IN (SELECT [id] FROM sysobjects WHERE xtype = @type AND status >= 0) AND [text] LIKE @find
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply