Click here to Skip to main content
15,867,777 members
Articles / Database Development / SQL Server
Article

SQL Server Object Search

Rate me:
Please Sign up or sign in to vote.
4.85/5 (16 votes)
22 Aug 20077 min read 66.7K   763   46   12
An application that seeks to emulate the F4 object search function from Query Analyser

Introduction

Here at Chase Software we use an automatically generated middle tier for our core application, so until recently I had been comfortably isolated from the all the gory details its data source, a SQL Server 2005 database. However, sometimes I need to know a bit about the database I'm working with, and it was here that I was slightly dismayed to discover that SQL Server Management Studio (SSMS) has no feature at all similar to the old Object Search tool included in SQL Query Analyser for SQL Server 2000. SSMS offers only a filter function on its Object Explorer, but this is painfully inadequate for scenarios where I would enjoy the old Object Search function. This can search over all object types, in all databases, where the SSMS filtering can only be applied to a single object type in a single database!

My growing frustration with the meagre filtering in SSMS and declining productivity prompted me to reproduce the functionality of the old Object Search tool in an external tool for SSMS. This article and the accompanying code are the result.

Background

All the real work of the search is done by the sp_MSobjsearch stored procedure. I gleaned this information by doing a trace while running the old Object Search utility provided in the 2000 versions of SQL Query Analyser. This stored procedure is not available in SQL Server 2005, but Query Analyser handled this gracefully by offering to install it the first time I tried to use the Object Search tool on a 2005 server. Accepting this offer installed the stored procedure and allowed me to run searches just like I used to. My application, however, is a little more demanding and you will have to install the procedure yourself using the script I provide courtesy of Microsoft.

I use the

ObjectSearch 
class to encapsulate all the actual search functionality. This class manages the data access operation and the search results, and exposes a set of properties for the search criteria. These properties are a sub-set of the parameters used by the sp_MSobjsearch stored procedure, as some of parameters are not used in this version of my application. After setting the search parameter properties, calling the PerformSearch method causes an ObjectSearch instance to execute the stored procedure and store the search results, which it exposes through its Results property.

The search parameter properties work as follows:

SearchKey The term to search for in object names. This value may include the normal T-SQL wildcard characters. An exception is thrown if this value is empty when PerformSearch is called.
DatabaseName The name of the database to search. If this value is null or empty, the current database is searched. To search all databases set this property to '*'.
ObjectType A bit field indicating a combination of object types to search for. This value is determined by performing a bit-wise OR on the bit field values for all object types to be included in the search. Object types are discussed below.

A value of 1 for the @status parameter indicates that the stored procedure should return a result set indicating its progress in for each step of its search process, but not only is receiving result sets asynchronously beyond the scope of this article, it is also far outside my SQL Server programming competencies. For this reason I also avoid using the @hitlimit parameter, as a hit limit is pointless once all results are returned by a synchronous execution of the procedure. The @casesensitive parameter, also an integer flag, tells the procedure to perform a case sensitive search, and this only works on case sensitive databases. I have forgone further efforts along this line in order to deliver an application that works on case insensitive databases, which I think most are. The @extpropname and @extpropvalue parameters specify information to search for in the extended properties of SQL Server objects, and I will be revisiting this functionality in a later version of this application.

Using the code

The Search Form

This is the main, and only, form in the application. It handles search the criteria and results, and presents commands to execute a search, stop a search, and clear the display for a new search. I have deferred implementing the Stop command until the next version of this application in order to spend a little more time dealing with the added complexity of using a separate thread to execute the search.

While the original Object Search utility included with the 2000 version of SQL Query Analyser uses drop downs for the object name (search key) and database fields, I have opted for single value text boxes in the interests of early delivery and simplicity. The currently disabled controls are retained on the form to maintain balance in the layout until the functionality they support is implemented in a future version of this application.

I use a set of

CheckBox
controls for the user to select combinations of object types to search on, and a single CheckBox to select or deselect all the object types. In the Tag property of each CheckBox I store an object type short name, which the form code uses to find an ObjectType struct in the static ObjectTypes property of the DataProvider class. This struct provides the bit field value of an object type, and the form uses the sum of these value for each selected object type to provide the
ObjectTypes
property value to its ObjectSearch when performing a search.

On completion of a search, the form binds the Results property of its to a DataGridView. The data grid presents the search results as one row for every SearchHit in the results collection, and provides niceties such as column re-ordering and sorting, but I will only implement sorting in the next version of the application.

Points of Interest

Object Types

The concept of object types is central to the search functionality of this project. Object types specify which objects are searched and indicate the types of the objects returned in search results. Object type values returned from the sp_MSobjsearch stored procedure all correspond to the object type values returned by the sys.objects catalog view, except for 'COL' and 'I' types, which denote a column or an index, respectively. The original Object Search utility seems to do some processing that replaces the short object type name returned by the search procedure with a longer, more descriptive name. I have used the long type names given in the SQL Server documentation for the sys.objects view. The object types used are listed in Appendix I.

The search procedure takes an integer representing the object types to search for, comprising a combination of the 2n bit field values representing each object type. The values are given in the comments at the top of the script for the sp_MSobjsearch procedure. Two of the types given there are not used in the original utility so I exclude these from this version of my application as well. An example of using these values is a search for a column in user tables and views. The bit field values are 1024 for a column, 1 for a user table, and 4 for a view, and if I do a bit-wise OR on these values I get an object type parameter value of 1029.

I use a structure called SqlObjectType to represent an instance of the object types mentioned above, and expose a collection of SqlObjectType objects through the static ObjectTypes property of the DataProvider class. The static constructor of this class initialises the collection using literal values for the object type properties. For object types not supported in the original Object Search utility I simply used a value of zero. I will eventually revise this code to read object type definitions from an external resource such as an XML file.

The UI presents a

Checkbox
control for each object type supported, as well as one to select all object types. I set the Tag property of each
CheckBox
to the short name of the object type it represents, enabling the me to get the bit field value from the DataProvider class using this name as a key. This lets me iterate through all the CheckBox controls, adding these values up, to get my object type parameter value for the search procedure. I welcome suggestions on how to better handle a scenario like this with bit fields and CheckBox controls.

History

This is v1.0. Revisions to follow soon pending feedback.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Founder Erisia Web Development
South Africa South Africa
I am a software developer in Johannesburg, South Africa. I specialise in C# and ASP.NET MVC, with SQL Server, with special fondness for MVC and jQuery. I have been in this business for about eighteen years, and am currently trying to master Angular 4 and .NET Core, and somehow find a way to strengthen my creative faculties.
- Follow me on Twitter at @bradykelly

Comments and Discussions

 
GeneralMy vote of 5 Pin
Kanasz Robert24-Sep-12 6:00
professionalKanasz Robert24-Sep-12 6:00 
Not bad
GeneralRe: My vote of 5 Pin
Brady Kelly24-Sep-12 20:41
Brady Kelly24-Sep-12 20:41 
GeneralMy vote of 1 Pin
arvinder_aneja29-Jan-10 23:45
arvinder_aneja29-Jan-10 23:45 
GeneralPragmaSQL Editor Pin
Ali Ozgur6-Aug-08 21:25
Ali Ozgur6-Aug-08 21:25 
GeneralRe: PragmaSQL Editor Pin
Jörgen Sigvardsson21-Apr-09 20:43
Jörgen Sigvardsson21-Apr-09 20:43 
GeneralSearch inside objects Pin
sylvain22224-Oct-07 3:40
sylvain22224-Oct-07 3:40 
GeneralMultiple servers Pin
Marc Schluper28-Aug-07 5:14
Marc Schluper28-Aug-07 5:14 
GeneralQuestion for installing sp_MSobjsearch [modified] Pin
W Zhang28-Aug-07 4:41
W Zhang28-Aug-07 4:41 
GeneralGreat Article Pin
rilov23-Aug-07 13:37
rilov23-Aug-07 13:37 
GeneralRe: Great Article Pin
Brady Kelly23-Aug-07 21:56
Brady Kelly23-Aug-07 21:56 
GeneralExcellent Pin
merlin98122-Aug-07 4:02
professionalmerlin98122-Aug-07 4:02 
GeneralRe: Excellent Pin
Brady Kelly22-Aug-07 4:09
Brady Kelly22-Aug-07 4:09 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.