Share your SQL Server tips and tricks with other SQL Server Magazine readers. Email your ideas (400 words or fewer) to r2r@sqlmag.com. If we print your submission, you'll get $50.

Using LIKE to Search for Columns
I've developed a stored procedure that I use to search for columns in a database by name; I call it sp_findfields. This stored procedure uses the LIKE keyword to perform the search and retrieves all the fields and table names that are similar to the argument you pass to the procedure.

Say you're looking for all the columns in your database that have ID in the column name (e.g., EmployeeID). First, use the code that Listing 1 shows to create the sp_findfields stored procedure. You can add the procedure to the master database so that it's available from all databases or just add the procedure to the database you're working with. Then, execute the following statement:

sp_findfields id

Figure 1 shows a sample of the output you get if you run the procedure in the Northwind database. And you can use the following statement to return all column names in the Northwind Customers table that contain the letters ID:

EXEC sp_findfields customers, id

Using LIKE is helpful to me when I perform searches because it's faster and more convenient than using the sysobjects and syscolumns tables.

—Ilyan Mishiyev
ilyan@igmcc.com