This tip continues the system stored procedure series with a routine to find occurrences of a string in the character columns of selected tables.

The SQL code in Listing 1 creates a system stored procedure named sp_FindString.  The routine searches the appropriate character columns (char, nchar, varchar, nvarchar) of a specified set of tables looking for occurrences of a given string.  The output result set includes table name, column name, and number of rows in which the string was found.

The sp_FindString stored procedure accepts eight parameters and only the first one is required.

The first parameter specifies the string to be found.  The string is compared to the contents of character columns using the LIKE operator, so all the wildcard capability of that operator is available.

The next four parameters work together to form a combination of search criteria using object names.  Please refer to my tip dated July 21 for an explanation of how these parameters work.  These parameters specify the tables to be searched.

The next (sixth) parameter specifies the minimum physical length for columns to be searched.  Character columns with a physical length of only one are always skipped because there are more efficient ways of looking for single character data.

The next (seventh) parameter specifies if nullable columns are to be searched.  A value of zero (0) means nullable columns are searched and a value of one (1) means nullable columns are skipped.

The last (eighth) parameter affects the output result set.  A value of zero (0) removes the prefix from table names (if a prefix is identified) and a value of one (1) does not.

The sp_FindString stored procedure uses a cursor.  A FAST_FORWARD cursor over a small set of data will often outperform other methods of handling row-by-row processing.  However, most typical data manipulation tasks can be performed with set-based processing which is usually (but not always) more efficient.  It's generally better to avoid any row-by-row processing in production data manipulation routines.  This routine is not intended for production use and it does not perform a typical data manipulation task.

The sp_FindString stored procedure uses dynamic SQL code.  As mentioned in a previous tip dynamic SQL code should be fine for an administrative routine.  This routine demonstrates some precautionary methods to protect the database from SQL injection because SQL injection is a possible risk with dynamic SQL code.  In this case the risk involves the first parameter, which contains the string to find.  The value of the parameter gets embedded in the dynamic SQL code.  If no validation is performed on the parameter value then it could potentially consist of malicious SQL code.  In order to mitigate the risk this routine performs three string substitutions on the parameter value to remove some common tricks.  Further, this routine puts some additional dynamic SQL code following the parameter value to cause a syntax error if/when the string substitutions are not sufficient to prevent an attack.

NOTE: The sp_FindString system stored procedure could run for a long time if/when it's used to search within many large tables.

This example finds every table/column that contains the word "Sales" in the Northwind database:

USE Northwind EXECUTE sp_FindString 'Sales'

I hope you find this system stored procedure to be useful.