In response to my post “Searching Your SQL Database for A Specified Column Name” I wrote a follow up article entitled ”Searching Your SQL Database For A Specified Column String” since as I mentioned in that post several people 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.
I answered that question in the Searching Your SQL Database for a Specified Column String post and that article too generated several e-mail inquiries. Some people asked if you could look for specific text (String) in a View and still others asked if it was possible to search procedures as well.
Unlike the Searching Your SQL Database for a Specified Column String solution this is actually quite easy to accomplish for both Views and Procedures. I created the query to look for both qualifiers in order to kill two birds with one stone as the saying goes.
I also added a Case statement to the SQL query below to make the results set for SysObjects.XType easier to understand so you can look in the ‘Views’ or ‘Stored Procedures’ leaf of the SQL Enterprise Manager (SEM) for the appropriate Name as defined in the location output.
Note: The SysComments.Text column is an NvarChar data type with a size of 4000 characters and contains the actual syntax that you would see in the SEM if you were to right mouse click on a view or stored procedure and select the ‘Properties’ tab.
To use the SQL query below change the line Where Text Like '%UserName%'
from '%UserName%' 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 ‘Where Text Like’ line to the keyword that you wish to locate and press the F5 keyboard shortcut to start the query parser.
SQL Query:
Select
SysObjects.Name Location,
'Object Type' = Case
When SysObjects.XType = 'P' Then 'Procedure'
When SysObjects.XType = 'V' Then 'View'
Else ''
End
From SysComments
Join SysObjects on SysComments.Id = SysObjects.Id
Where Text Like '%UserName%'
And SysObjects.XType In ('P', 'V')
Group by SysObjects.Name, SysObjects.XType
No Comments