SQL Server Code Search Tool

Question

I I wanted to search all stored procedures for the occurence of a substring "x", can I do this in SQL 2000? 2005? 2008?

..without any add-ins?

If not, what are the best available free add ins for each?

Sorry. I should have mentioned that I am aware how to use sysobjects data to do this. But it seems to me there should be a nice GUI in front of this functionality!

FOR EXAMPLE, Something like this:

http://screencast.com/t/Y2Q0YmU0Nzkt

Duplicate: http://stackoverflow.com/questions/1364633/search-for-a-key-word-in-all-the-stored-procs-for-a-given-db — OMG Ponies, Nov 18 '09 at 04:34
Sorry. I should have mentioned that I am aware how to use sysobjects data to do this. But it seems to me there should be a nice GUI in front of this functionality! — Chad, Nov 18 '09 at 04:35

score 2 · Answer 1 · edited May 23 '17 at 10:27

2

Also answered here

From http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=32319

CREATE PROCEDURE sp_FindText @text varchar(8000), @findtype varchar(1)='P' AS
SET NOCOUNT ON
IF @findtype='P' SELECT DISTINCT Len(SubString(text,1, PatIndex('%' + @text + '%', text)))-Len(Replace(SubString(text,1, PatIndex('%' + @text + '%', text)),char(13),''))+1 AS Line,
--PatIndex('%' + @text + '%', text) AS Position,
OBJECT_NAME(id) AS ProcName
FROM syscomments
WHERE text like '%' + @text + '%'
ORDER BY ProcName, Line

IF @findtype='C' EXEC('SELECT TABLE_NAME + ''.'' + COLUMN_NAME AS TableColumn FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE ''%' + @text + '%'' ORDER BY TableColumn')

IF @findtype='T' EXEC('SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ''%' + @text + '%'' ORDER BY TABLE_NAME')
GO

It not only searches procedure and view definition text, it will also find tables, views, and column names:

EXEC sp_FindText 'myTable' --or-- EXEC sp_FindText 'myTable', 'P' --finds procedures/views containing 'myTable' in their definition/code
EXEC sp_FindText 'myTable', 'T' --finds tables/views containing 'myTable' in their name
EXEC sp_FindText 'myCol', 'C' --finds columns containing 'myCol' in their name

edited May 23 '17 at 10:27

Community

1
1

answered Nov 18 '09 at 04:33

Dave Barker

6,303
2
24
25

syscomments only stores nvarchar(4000) so this fails if the search string of 4 letters starts at 3999 so this is unreliable – gbn Nov 18 '09 at 05:08
Please stop voting up a patently wrong answer that will give misleading results – gbn Nov 19 '09 at 18:39
I'll admit that this could miss a search term if it goes across a 4000 character boundary. Looking at a quick sample of 2 of our databases, one has 14 out of 606 and the other has 33 out of 468 objects greater than 4000 characters. I think you'd have to be pretty unlucky to miss a search term. In lieu of anything better I'm happy with this approach. – Dave Barker Nov 19 '09 at 23:16
@Dave: Perhaps unlucky, but when it does happen it could really bollix you up. I learnt my lesson years ago... – gbn Nov 22 '09 at 16:50

score 1 · Answer 2 · edited May 23 '17 at 10:32

For SQL Server 2005 and 2008, use sys.sql_modules or OBJECT_DEFINITION which supply nvarchar(max) output

SELECT OBJECT_NAME(object_id) FROM sys.sql_modules WHERE definition LIKE @MyString
SELECT name FROM sys.objects WHERE OBJECT_DEFINITION(object_id) LIKE @MyString

For SQL Server 2000, you have to use sycomments or INFORMATION_SCHEMA.ROUTINES, but they supply nvarchar(4000) so are unreliable. You'd have to concat sys.comments into a text value and search that (and frankly I've forgotten how to do it off top of my head)

Other questions where exactly the same issue is mentioned

score 0 · Answer 3 · answered Nov 18 '09 at 04:32

if i were you i wouldn't use a tool - just run a script to do the search for you, i.e., the following SQL script will search all stored procs in a db for a string

Declare @search varchar(128)

SET @search = '%' + 'search text' + '%'


/***************************************************************************/

SELECT o.name As "Stored Procedures"

            FROM SYSOBJECTS o INNER JOIN SYSCOMMENTS c
                        ON o.id = c.id
            WHERE c.text LIKE @search
                        AND o.xtype = 'P'
            GROUP BY o.name
            ORDER BY o.name

/**************************************************************************/

In this instance the xtype 'P' refers to stored procedures

syscomments only stores nvarchar(4000) so this fails if the search string of 4 letters starts at 3999 so this is unreliable — gbn, Nov 18 '09 at 05:09

SQL Server Code Search Tool

3 Answers3