Searching for text in stored procedures and functions in SQL Server

The following query comes in handy when you need to find which user-defined stored procedures or function definitions contain a certain string.


SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%<StringToSearch>%' 


This will return a list of names and definitions of all stored procedures and functions whose definition contains the string <StringToSearch>. If you wish to limit the results to stored procedures only then append the clause:

    AND ROUTINE_TYPE='PROCEDURE'

Likewise, if you wish only functions to be returned then use:

    AND ROUTINE_TYPE='FUNCTION'

Row order in Transact-SQL (SQL Server)

Including a column with the row number in the results of an SQL query is a pretty useful feature and the syntax is a bit hard to remember, so here is an example:


SELECT 
    ROW_NUMBER() OVER (ORDER BY <ColumnName> ASC) AS ROWID
    , * 
FROM <TableName>


This query returns all the rows of table <TableName>, including an additional column ROWID up front. The column <ColumnName> is used for determining the order in which the rows are numbered.

This feature works in SQL Server 2005 and later.