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.
