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 below 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 the table name, column name and number of rows in which the string was found.
The sp_FindString stored procedure accepts eight parameters. 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 the wildcard capability of that operator is available.
The next four parameters (second through fifth) work together to form a combination of search criteria using object names. Please refer to my stored procedure Find columns and tables to understand 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 to look 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 or when the string substitutions are not sufficient to prevent an attack.
NOTE: The sp_FindString system stored procedure could run for a long time when it is used to search within many large tables.
The following example finds every table or column that contains the word "Sales" in the Northwind database.
USE Northwind EXECUTE sp_FindString 'Sales'
ABOUT THE AUTHOR
Brian Walker is a senior database architect in an IS department that uses SQL Server 2000 and the .NET Framework. He has more than 25 years of experience in the IT industry with the last several years focused on databases and SQL Server. Walker is a software developer, database developer, database administrator and database consultant. He develops utility software as a hobby, including a large collection of SQL Server utilities.