Striving to be the person my dog thinks I am.

Physical database objects are often treated likely the shirttail cousin of modern, object-oriented software development.  How often have you peaked below the covers of a seemingly well-designed and well-constructed application, and been appalled and totally baffled by the underlying physical database structure, with its mish-mash of CustInfoTbl, GENERALINFO and STUD_MASTER (as in student master) tables.  Some would argue that this nastiness can be wrapped up in a data access layer, and the underlying physical structure never seen again.  But the data in your application has to be physically stored somewhere  sooner or later, so you’re likely to run into the structure of that data at some point.  When you do, you’ll have to hope that the person who created it — maybe even you? — followed some simple and predicatable standard so that you can quickly and effectively unravel its mysteries.

In addition, SQL Server 2005 and just about every other database encourages the incorporation of an increasing amount of program logic right into the database itself.  Stored procedures, scalar and table-valued functions, views and other objects — with their increasingly sophisticated programming constructs — means more and more of what we traditionally expect to find in the application layer(s) will be done at the database level using tight, journalized transactions.  Further evidence of this trend is the fact that you can now write your stored procedures in C# directly.  Poorly named physical structure in your database will guarantee that your increasingly complex stored procedures will be ugly and obtuse, as well.

In the past, there were some pretty good excuses for having a lousy set of names for your physical data.  Even some relatively modern databases placed numerous restrictions around the length of name and characteristics that could be used.  In other cases, physical structure was inherited from legacy systems that had even more draconian naming rules.  In the interest of expediency, the old structure names were simply brought into the new database software without change.  Sadly, I have had the personal misfortune of seeing COBOL table structures adopted carte blanche into SQL Server tables — for goodness sake, what was the point of that?  Furthermore, modifications to the structure may have caused even more damage over time.  But a modern database tool not only has a liberal and flexible policy around naming of structures, it usually provides some fairly good tools for modifying them over time.  Hence, there is no longer any good excuse for substandard naming of database structures.

If you need still more convincing, experience has shown that decisions around database object names tend to subconciously influence higher level structures.  In other words, a poorly named table will tend to lead to a poorly named class in the data access layer, which may lead to poorly named application that employs that class.  I know in the object-oriented world that it really shouldn’t work that way, but it likely happens more often that most care to admit.  Clearly named physical structure encourages clear-headed thinking about the rest of the application.  Also, with ever-improving hardware and network performance, I see a future where the logical and physical structure of the data are almost the same.  At the very least, they will start out the same, and only grow apart to address specific issues of performance and functionality.

I have iterated through a series of naming standards for database objects over the years, and I finally stabilized on one particular approach, which I now follow with little variation.  In the next and subsequent  series of posts, I will attempt to methodically document the standard that I now use when creating physical database structure.  It’s hard to tell how long the series will run, but over the course of it, I hope to be able to address most aspects of physical design; tables and columns will constitute the first couple of posts, then procedures and functions, and then beyond that, I’m not quite sure.  I’ll just keep going until I run out of valuable things to say on the subject.

Of course, I encourage you, the reader, to contribute your thoughts, and I’ll do my best to respond to those in this series of posts. At the very least, tell me that I’ve got it all wrong, and I’ll do my best to further explain my logic.  If you present a better idea, I will certainly incorporate that thinking into the ideas presented.  First up, table naming standards, and until then, thanks for reading.
 

I tend to push a fair amount of T-SQL code into the SQL Server database, so one of the things I find myself wanting to do fairly frequently is query the text of objects that have been created.  For example, let’s assume that a particular table name has changed, and you want to find all references to this table in the code of your stored procedures, functions, views and other database objects.  This rather mundane, but important, capability seems to be overlooked with the SQL Server team.  The solution, under normal circumstances, is to dig around SQL Server metadata.  Complex, and prone to error, it would seem.

SQL Server, of course, has system tables that contain this information, but there is some quirkiness in how it stores it.  The text of most (but not all) objects is stored in the text column of the syscomments table.  However, long texts will be split over multiple rows — likely a holdover when procedures were likely a whole lot simpler than they are today.  There’s a temptation to simply use a LIKE clause against this column, but you may miss an instance of the target string if it just happens to be split across multiple rows.  To avoid this, you really have to reassemble the rows into one long line of text.

While solving this problem, it also makes sense to do the necessary JOINs to bring together row from related system tables so that you actually have some result rows with useful information about the database objects.  The view DatabaseObject.sql responds to these requirements.

Its very simple to use.  Of course, you’ll first need to create the view using the .sql file attached.  Then, let’s say you want to find all instances of the table name dbo.Person, in all of database objects.  You would use something like the following.

SELECT
	*
FROM
	dbo.DatabaseObject
WHERE
	txt LIKE '%dbo.Person%'

Just like any old, simple table; no fuss, no muss.  The SQL Server documentation has lots of useful information on the LIKE clause that will allow you to find just about any combination of characters.

The next post will provide some additional information about how and why the code looks the way it does, but for the time being, you now have a method of tracking down those pesky strings of text that may have been missing in the past.   Have fun!