DbViewSharp in the Field

I may have mentioned this before, but I use DbViewSharp every day. That's why it has become a Swiss army knife of an application. In an attempt to promote it amongst my colleagues (all die hard Sql Management Studio users alas) I tend to fire it up at every opportunity to answer any database-related questions they may ask me. This sometimes misfires, but occasionally scores a hit. Today was an example of the latter.

A colleague was asking my opinion on the typical number of sub-records would be attached to a main record in a certain schema. Rather than make an educated guess I switched to DbViewSharp (already running of course), connected to the relevant database

sought the relevant sub-record table

and opened the data view. The sub-record includes a sequence number field so I selected a cell in that column and pressed the aggregation toolbar button.

The effect of this is to produce a sorted count of all the instances of the sequence number.

From this we could see that every main record contained a sub-record with sequence #1, but only 5% or so contained sequence number #2 with subsequent numbers dropping off to a maximum of around 100 sub-records associated to a main record. Below is the core of the query used to produce the grid:

select SEQ,
       count(SEQ) as [COUNT]
from SUB_RECORDS
group by SEQ
order by count(SEQ) desc, SEQ

DbViewSharp adds a few exotic touches such as a row limiter, some dodgy code to enable the query to count null records and a search clause to enable you to apply a filter. Since this aggregation function can be applied to most types (don't try it on Xml and binary types) it's useful for obtaining an overview of how any field with a limited range of values is populated; eg. proportion of 1 or 0 entries in a flag field, most common state in a US customer table, customers with the largest number of orders or sales and so on. In many cases using this feature avoids having to write a custom query. A further nice touch is that if you happen to discover an unexpected value in the report (a number out of range or an unusual text value)

then just double click on the row with the odd value to display all of the row data for any record that contains that value.


This can sometimes be the start of a trail to a problem in your data.

DbViewSharp is an open source desktop application that connects to SQL Server databases and allows you to search for just about anything important. Find it on Codeplex.

What is DbViewSharp and why would you use it?

DbViewSharp is an open source desktop application for searching SQL Server databases. But then so is SQL Management Studio. So why do I use DbViewSharp instead of SQL Management Studio (SMS)?

When I want to find out something about a database; the fields in a table, the data in a table, the source of a view or stored procedure, I want it fast; there's usually an urgent problem. I don't like waiting for my program to load then finding the connection or even re-entering the connection details; then expanding the list of database names (why so slow?); then finding the database name in the list of databases; then expanding the list of table names; then finding the table name in the list of tables; then right-clicking and finding the item in the context menu to show the data; then choosing whether to select 1000 rows to view or 200 rows to edit. Then waiting while the application fetches 4, just 4, rows of data. I've just done this on SQL Management Studio and it was painful. While I'm on a slow machine at the moment I'm going to try the same thing on DbViewSharp.

Ok then, launch DbViewSharp, it's faster to launch, but not so you'd notice. Click to connect to the database and right there are the tables. Find the table, double-click on it and right there is the data; pretty much instantly for a table with 4 rows in it.

You want to see the source for a view? With SMS you expand the list of views, you find the view you want, right-click to get the context menu, search down the list of options, figure out you want "Script view as...", then Create to..., then New query window, wait for a moment, and finally see your view source. I wont bother describing what you have to do to compare the source of the same view in another database. In DbViewSharp, click on the views toolbar button and you see your views. Double-click on the view and you see the source. That's all.

SMS is a mighty application, which does (almost) everything that DbViewSharp does and much, much more. However it was designed so you could manage every single aspect of a SQL Server database through a GUI and compromises its search functionality with its CRUD features and all-inclusivity. DbViewSharp is tuned for searching, which is what I've wanted most for the last 8 years of working with databases.

I hope the examples I've run through have given a idea of the different design approach I took with DbViewSharp. There are so many more cases where I've taken the liberty of making a common search task easy to perform possibly at the expense of omitting some functionality I need once in a season.

I hope to expand on this in future posts, but for now if you want a change from  SQL Management Studio  and are tired of looking at endless clones based around a tree of database objects and a query window then give DbViewSharp a try. Find DbViewSharp on Codeplex.