September 6, 2005 at 9:54 am
Hi,
I'm running sql server 2000. I have a couple hundred store procedures. I was wonderding if there was a way to look for a word in the store procedure without opening all the store procedures. Like a find in notepad or if anyone knows where the store porcedures are store.
Thanks
Mark
September 6, 2005 at 9:56 am
Select DISTINCT O.Name from dbo.SysObjects O inner join dbo.SysComments C on O.id = C.id and O.XType = 'P' WHERE C.Text like '%SearchString%'
September 6, 2005 at 9:57 am
DECLARE @codesnippet varchar(100)
--Amend here to insert wht you are looking for
SET @codesnippet = 'wantedword'
SELECT DISTINCT LEFT(DB_NAME(), 20), (LEFT(OBJECT_NAME([id]), 40)) AS ObjectName
FROM dbo.syscomments
WHERE [text] LIKE '%' + @codesnippet + '%' AND OBJECTPROPERTY([id], 'IsMSShipped') = 0
ORDER BY ObjectName
This is my routine, includes triggers and other objects as well
September 7, 2005 at 6:28 am
free tool has been posted on this site many times. SQL Digger
September 7, 2005 at 8:35 am
Got a simple little stored proc here that does a nice job of grabbing the proc name and the snippet of code containing the search term.
I also created one that looks at views as well with a simple code change.
Enjoy
CREATE proc dbo.sp_ProcTextSearch
(
@search_string varchar(128)
)
AS
DECLARE @strSQL varchar(350)
DECLARE @strSQL2 varchar(300)
DECLARE @dbname sysname
DECLARE @x varchar(257)
DECLARE @count int
SET NOCOUNT ON
BEGIN
/*-------------------------------------------------------------------
** Procedure Name: dbo.sp_ProcTextSearch
** Date Written: 10/03/2002
**-------------------------------------------------------------------
** Input Parameters:
** @search_string = string to search on
** Input Tables:
** none (just procedures)
** Output Parameters:
** Output Tables:
** Return Status: returns table name and line where text is found
**--------------------------------------------------------------------
** Comments:
**
**-------------------------------------------------------------------*/
-- GET ALL PROC NAMES INTO #ONE
SELECT b.[name] + '.' + a.[name] as spname
INTO #one
FROM sysobjects a INNER JOIN sysusers b
ON a.uid = b.uid
WHERE xtype = 'p'
DECLARE cone CURSOR FOR
SELECT spname
FROM #one
-- Proc text table
CREATE TABLE #spcount (sptext varchar(1000))
-- complete proc name and text table
CREATE TABLE #final (spname varchar(128), sptext varchar(1000))
OPEN cone
FETCH NEXT FROM cone INTO @x
WHILE @@FETCH_status=0
BEGIN
-- Get the text
TRUNCATE TABLE #spcount
SET @strSQL = 'insert #spcount EXECUTE sp_helptext ''' + @x + ''''
EXECUTE(@strsql)
-- Populate the table #final with proc name and the text
INSERT #final
SELECT @x, sptext
FROM #spcount
FETCH NEXT FROM cone INTO @x
END
CLOSE cone
DEALLOCATE cone
SET @strsql2 = 'SELECT spname, sptext
FROM #final
WHERE sptext like ''%' + RTRIM(@search_string) + '%''
ORDER BY spname'
EXECUTE(@strsql2)
END
GO
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply