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
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
End
Close Column_Cursor
DeAllocate Column_Cursor
Fetch Next From Table_Cursor Into @Object
Close Table_Cursor
DeAllocate Table_Cursor
Select "Table Name", "Column Name" From #TableColumns
Drop Table #TableColumns
No Comments