Share This Post

Searching Your SQL Database For A Specified Column String

In response to my post “Searching Your SQL Database for A Specified Column Name” many people have asked me if it is possible to go further into the SQL database to find not just a specified column header but actual SMS captured data such as an individual user or computer name.

To answer that question yes it is possible but it can become very complicated. We have to make use of Cursors to loop through tables and create temporary SQL tables to place all of the results into an array of sorts and then in turn enumerate that array to get the desired result set back from the query parser.

Note: In SQL server a Cursor, whether it is cursed or blessed by SQL DBA’s, is simply a result set that allows you to recursively loop through elements in a row by row operation on a returned result set. Cursors must adhere to specific rules and must include the following flow: First you must declare or set the Cursor, then open the cursor to gather information, then fetch or grab information from it and then finally close or deallocate the cursor to terminate it.

To use the SQL query below change the line Set @Locator = ‘%Don Hite%’ from %Don Hite% to the string that you want to search for. As was mentioned in my original post the percent symbols are set to function as wildcard placeholders. You can either retain the percent symbols or remove one or both of them as you wish.

To run or execute the SQL query below simply copy and paste the SQL query below into the SQL query analyzer (Isqlw.Exe from the start > run line) use the database dropdown arrow to select your SMS database change the Set @Locator = to the keyword that you wish to locate and press the F5 keyboard shortcut to start the query parser.

 

SQL Query:

 

Declare @Locator VarChar(50)

Declare @Object Int

Declare @Column VarChar(50)

Declare @Qry NVarChar(500)

 

Set @Locator = ‘%Don Hite%’

Create Table #TableColumns

("Table Name" VarChar(50), "Column Name" VarChar(50))

 

Declare Table_Cursor Cursor For

Select Id From SysObjects

Where XType = ‘U’

 

Open Table_Cursor

Fetch Next From Table_Cursor

Into @Object

 

While @@Fetch_Status = 0

Begin

 

Declare Column_Cursor Cursor For

Select SysColumns.Name

From SysColumns,SysTypes

Where SysColumns.Id = @Object

And SysColumns.XType = Systypes.XType

And Systypes.Name In (char, nchar, nvarchar, ‘varchar’)

 

Open Column_Cursor

Fetch Next From Column_Cursor Into @Column

 

While @@Fetch_Status = 0

Begin

Set @Qry = ‘Insert Into #TableColumns ‘

+ ‘Select Distinct ”’ + Object_Name(@Object) + ”’, ‘

+ ”” + @Column + ”’ ‘

+ ‘From [‘ + Object_Name(@Object) + ‘] ‘

+ ‘Where Exists (Select 1 From [‘ + Object_Name(@Object) + ‘] ‘

+ ‘Where [‘ + @column + ‘] Like ”%’ + @Locator + ‘%”) ‘

Exec Sp_ExecuteSql @Qry

Fetch Next From Column_Cursor Into @Column

End

 

Close Column_Cursor

DeAllocate Column_Cursor

Fetch Next From Table_Cursor Into @Object

End

 

Close Table_Cursor

DeAllocate Table_Cursor

 

Select "Table Name", "Column Name" From #TableColumns

Drop Table #TableColumns

Share This Post

Don Hite began writing as a columnist for Rod Trent on SWYNK.Com (Now Enterpriseitplanet.Com) in the winter of 1999. In January of 2002 he co-founded the Kansas City Regional Systems Management Users Group (KCRSMUG) along with Janis Keim. Don is the eldest of four children and was born in the Army hospital at Wurzburg Germany in November of 1957. After living in Bad Kissingen Germany for the first few years of his life the Hite family moved back to the United States after his father retired from the army when Don was still in kaki army diapers. Educated by trade as a commercial master electrician Don made the career change from terminating Copper Conductor cable to terminating Ethernet cable in the early 1990’s. Don is married and lives on a farm in rural Kingsville Missouri with his wife Virginia (Ginny) and has a Son Lee, Stepdaughter Lisa and an 11 year old grandson named Blake.

Leave a Reply