Doing a keyword search inside the contents of the stored procedures of a database

Is there a way in sql to do this:
To get all the stored procedures of a given database, then either
- (1) put all the stored procedures contents (all the scripts inside the SPs) in a single file or
- (2) Put all the scripts that constitute all the SPs in a single big string.
Then search that file (if case 1) or that big string (in case 2) and check if it includes a given input pattern (for example check if includes: mySearchedKeyword)

Thanks a lot

This is fairly straight-forward. Here is one method:

SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%mySearchedKeyword%'

|||Thanks a lot|||

I need to do the search from a table: myKeyWordsTbl which has only one column and many rows.

So myKeyWordsTbl is like this:

KeyWord1

Keyword2

Keyword3

....

For each row (only 1 column) of the table myKeyWordsTbl. I need to take that row value and search if it exists in any of the stored procedure included in all the databases of that instance.

For each keyword that I find, i need to mention the name of the database, the stored procedure and if possible the line number where that row value occured in the SP.

Any guidelines pls

Thanks a lot.

|||

another question pls:

I wanna customize the code:

SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%mySearchedKeyword%'

to search all databases and not only the current one.

Thanks.

|||

You can use the INFORMATION_SCHEMA view only if the SP text does not exceed 4000 characters otherwise the definition will be truncated. So to account for any length you need to do something like sp_helptext logic. This will work in any version of SQL Server. In SQL Server 2005, you can simply use the OBJECT_DEFINITION meta-data function. This returns the entire definition of any valid object as nvarchar(max) value. See below link for more details.

http://msdn2.microsoft.com/en-us/library/ms176090.aspx

|||Thank you