Friday, October 9 2009, 13:33
How to search Objects in SQL Server 2005/2008
Software evolves… new versions of major software are released once every few years, they contains improvements, bug fixes, but sometimes, some features disappear just like they appeared… without any seemingly valid reason…
So is the case, with the useful “Object search” feature, which was present in SQL Server 2000 Query Analyzer but somehow didn’t make its way into SQL Server 2005 and 2008’s Management Studio.
In addition to this improvement, Management Studio (SSMS), uses the Visual Studio shell, which leverages all the development work already done on Visual Studio, especially in the aspect of the code editor. This undoubtly allows Microsoft teams to not reinvent the wheel every time (syntax highlighting engine, etc). Actually, even third-party tools can use the Visual Studio shell for their own applications, but the runtime is a bit bulky and the object model probably overwhelming and deemed overkill for most developers which probably explains why this practice isn’t very widespread yet for third-party tools.
During this transition, a lot of features were added, and SSMS feels like a superior product, but unfortunately, a feature seems to have escaped during this metamorphosis: Object Search.
Object Search is very useful if you need to know where a database object resides, be it a table, view, stored procedure, user-defined function and so on. When you have developed the project you are in charge in yourself or when the project is very well documented, you probably won’t need to use it that often, since after all, you already know where the objects you created are. However, when you get in charge of a software project from a previous developer, who oftentimes left the company and is not available for support or answering your questions anymore, and didn’t let a lot of documentation behind himself before leaving, you are on your own. In the end, you’ll get to know the database better, but at first, you do not know at all where almost anything resides in. Let’s imagine you need to add features to a stored procedure which is adding data to a table. You know the table name which is modified, but have no idea which of the 350 available stored procedure touches it. Obviously going through each of them would take considerable time, so you need to resort to a more automated way of doing things.
When it happened to me, I remembered that feature from SQL Server 2000’s Query Analyzer, but it was nowhere to found: I really thought that it to be here… somewhere… Eventually, not finding it, I resorted to do a Google search and I ended up to this page at Microsoft Connect (some sort of public bug-tracking tool from Microsoft), which breaks the bad news: this feature was gone! Some employees, apparently from the SQL Server team, commented that it would be added back in SQL Server 2005 SP2, then the next major version (ie. 2008), but at the time of writing, we are at 2008 SP1 and the lost child still didn’t find his way home… It was time to resort to another solution:
- Install SQL Server 2000 Query Analyzer back: Query Analyzer, may be old, but it is still able to connect to SQL Server 2005 and 2008 instances just fine, so if you still have it installed on your machine, it is probably the quickest way to go. This approach didn’t please me since I thought I was done with SQL Server 2000 and didn’t want to add it back to my machine. It is also kind of a pain to have a software just for a single feature and switch to that one every time this feature is needed.
- Use queries stored procedures to do this kind of searches: some already made stored procedure exist on the Internet: the problem is that you need to remember the name and syntax of the stored procedure and that most of those found on the Web will search one object type at a time. Of course, they may be modified and improved and tweaked to be more generic or adaptable to your needs, but you need to spend time on this… for a feature that was right at your fingertips 9 years ago…
- Use a third-party tool: this is the last solution I chose. xSQL, a company providing third-party SQL Server tools, has a free software compensating for that missing feature, and even way beyond what the original could do. It doesn’t feel as integrated as an actual feature built into SSMS would, but if you add the executable to the Tools menu of SSMS, the difference is negligible.
Hopefully, this feature will be added back in the next SQL Server 2008 Service Pack, but at least, I have an easy and convenient workaround until then…