Stored procedure: Search character columns for a given string

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 alwa

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Database Development Managing the development lifecycle with Visual Studio Team System 2008 Processing XML files with SQL Server functions A first look at Visual Studio Team System 2008 Database Edition How to create a SQL inner join and outer join: Basics to get started New datetime data types in SQL Server 2008 offer flexibility Using DATEADD and DATEDIFF to calculate SQL Server datetime values SQL Server database design disasters: How it all starts SQL Server database design disasters: What not to do Secure SQL Server from SQL injection attacks Check SQL Server database and log file size with this stored procedure SQL Server Stored Procedures Top 10 SQL Server development tips of 2008 SQL Server trigger vs. stored procedure to receive data notification SQL Server errors, failures and other problems fixed from the trenches SQL Server and data manipulation in T-SQL How to use SQL Server 2008 hierarchyid data type SQL Server stored procedures tutorial: Write, tune and get examples Check SQL Server database and log file size with this stored procedure SQL Server source code analysis and management adds database security Configure SQL Server Service Broker for sending stored procedure data Find size of SQL Server tables and other objects with stored procedure SQL/Transact SQL (T-SQL) Working with sparse columns in SQL Server 2008 Determining the source of full transaction logs in SQL Server New GROUP BY option provides better data control in SQL Server 2008 Using the OPENROWSET function in SQL Server Loading data files with SQL Server's BULK INSERT statement Importing and exporting bulk data with SQL Server's bcp utility Testing transaction log autogrowth behavior in SQL Server Securing SQL Server with access control, login monitoring and DDL triggers Top 10 SQL Server development tips of 2008 The sqlcmd utility in SQL Server SQL/Transact SQL (T-SQL) Research

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary library  (SearchSQLServer.com) trigger  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

ys) 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.

Example: sp_FindString

The following example finds every table or column that contains the word "Sales" in the Northwind database.

USE Northwind EXECUTE sp_FindString 'Sales'

Click for the stored procedure: sp_FindString

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.

More information from SearchSQLServer.com