CODEWITHSUNDEEP
sql-server
Jin Yong
Jin Yong

Reputation: 43768

Text search in stored proc SQL Server

Does any one know the script to use for text search in SQL Server? I would like to search a text from all the stored proc inside the SQL Server, does anyone know what is the script I should use?

Upvotes: 7

Views: 16413

Answers (4)

gbn
gbn

Reputation: 432180

INFORMATION_SCHEMA.ROUTINES or syscomments are not reliable.

The text field is nvarchar(4000) for both (over multiple rows syscomments only). So your search text can be lost on the boundary for a syscomments or never found for INFORMATION_SCHEMA.ROUTINES

sys.sql_modules.definition is nvarchar(max)

SELECT
    OBJECT_NAME(object_id)
FROM
    sys.sql_modules
WHERE
    definition LIKE '%mytext%'

Edit, Oct 2011

Bringing this answer up to date.

Red Gate SQL Search is a free SSMS plug in that is quite useful.

Upvotes: 13

Mitch Wheat
Mitch Wheat

Reputation: 300489

Updated: There are several equivalent ways. Here's one:

SELECT 
    OBJECT_NAME(object_id) 
FROM 
    sys.sql_modules 
WHERE 
    Definition LIKE '%searchtext%' 
    AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1

Upvotes: 2

ashish.chotalia
ashish.chotalia

Reputation: 3746

You can use as well:

select distinct object_name(id) from sys.syscomments where text like '%SearchTextHere%'

Upvotes: 2

CoreyStup
CoreyStup

Reputation: 1488

Are you wanting to search for text through the stored procedures themselves?

Or table data?

If table data, how about LIKE?

Upvotes: 0

Related Questions