What's the name of the SQL SP that locates a text string?

Question

I am very embarrassed but I cannot remember the name of the MS SQL Server SP that searches your DB for a specified text string and returns all the table names, functions, stored procedures etc that contain that string. And I can't look it up in the help, because in order to get help on it, you need to know its name! It was something like sp_findtext.

What is it called?

Come on, fastest fingers gets answer credit! :)

There is such an SP? Damn, I wrote such a thing myself because I thought there wasn't anything like that... — Maximilian Mayerl, Oct 22 '09 at 09:16
Ah, turns out there isn't - I thought there was but someone else in my last job had written it... :) — Shaul Behr, Oct 22 '09 at 09:33

score 2 · Answer 1 · edited Jan 21 '23 at 15:03

2

I found a custom SP (FindTextInDatabase) that does exactly that:

Link

Don't know if there's a ready-made procedure also. If there is one, I can't find it either.

edited Jan 21 '23 at 15:03

Glorfindel

21,988
13
81
109

answered Oct 22 '09 at 09:17

pyrocumulus

9,072
2
43
53

score 2 · Accepted Answer · answered Oct 22 '09 at 09:21

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

But wrong... 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

score 0 · Answer 3 · answered Oct 22 '09 at 09:21

0

Have a quick check on the results of these:

select * from sysobjects where [name] like 'sp_%' order by 1
select * from master..sysobjects where [name] like 'sp_%' order by 1

You find what is it for you.

Cheers!

answered Oct 22 '09 at 09:21

jerjer

8,694
30
36

You know that "ORDER BY 1" is completely useless, don't you? – Maximilian Mayerl Oct 22 '09 at 11:58
How is it useless? It orders by the first output column. – Tadmas Oct 22 '09 at 13:22
It would would be completely useless if you don't know what it is. – jerjer Oct 23 '09 at 02:20

What's the name of the SQL SP that locates a text string?

3 Answers3

Linked