Welcome to Aussie SQL Server Bloggers Sign in | Join | Help

Using column name prefixes

I was working on some database documentation for a customer recently & noticed something very handy about having columns named uniquely in a database which might be worth taking into consideration for your naming standards documentation.
 
In the database I was working on, a table name indicator is prefixed to column names to "uniquefy" them. Eg, the first_name column in the customers table is named [cus_first_name]. In the employees table, the column would be named [emp_first_name]. This allows a simple table / column / procedure mapping query to be run which documents which table.columns are accessed by which procedures & vice versa, eg:
 
select distinct pname, tname, cname
from  (select so.name 'pname', sp.text 'ptext'
       from syscomments sp
       join sysobjects so on sp.id = so.id
       where so.xtype = 'P') procs
    , (select so.name 'tname', sc.name 'cname' 
        from   sysobjects so
        join   syscolumns sc on so.id = sc.id
        where  so.xtype = 'U') cols
where charindex(cols.cname, procs.ptext, 1) > 0
 
This provides a nice easy to read list in the format:
[TableName], [ColumnName], [Procedure]
 
Very handy for determining dependency & producing documentation. I put the output into Excel, threw on an auto-filter & now I can easily see which table / columns are accessed by which procedures & vice versa, which really helps with what I'm doing today.
 
If column names weren't unique in the database though, this wouldn't be possible without a TSQL parser. The uniqueness allows simple pattern matching rather than reliance on TSQL parsers (which aren't readily available)
 
Is this a good thing to include in a naming standards document? I guess there are arguments for & against doing this but this has been very helpful to me today so I thought I'd put it out there for others to comment on.
 
Personally, I like the idea as it also avoids the need for specifically aliasing columns in queries where multiple relations are joined, which is a discipline which is often neglected under the heat of development pressures.

Published Tuesday, 1 November 2005 8:39 AM by Greg_Linwood
Filed Under:

Comments

# re: Using column name prefixes

I read a few of your other posts, some nice stuff in there, but this one makes me see red.

Please, dont do that! I am not going to talk about the insane irritation experienced when you have a system with some 100+ tables, all using their own distinct prefix, and to constantly have to look up the column names etc for simple coding/scripting etc (been there, got the t-shirt and prozacs). Any domain/attribute should:
a) have the same name everywhere it occurs
b) have the same datatype/length/rules etc

really, or one will get lost in the mess that is 100% guaranteed to follow.

Now, I can sympathize with what you want to achieve here, looking up dependencies easily etc, but you should really ask yourself if perhaps your process of implementing code should be looked at instead (inline comments that can be extracted are but one of the easy ways to achieve what you are after).

second thing is, it would be a better option, in my view at least, to have a predefined unique alias for all tables (3-4 letters), used consistently in all your sp's instead. Qualifing column names with their source table name/alias is a good practice that should be encouraged in the departments. In fact, build up your own little table/object dictionary, add the alias as an attribute, and you could link that table in your query above, and just alter the last where clause to something like:

charindex(mydictionary.alias + '.' + cols.cname, procs.ptext, 1) > 0

I still believe, after tried most options and tools available i guess by now, that inline comments adhering to a simple scheme (xmlcomments or similar), later to be extracted is by far the best way to easily extract comprehensive reference documentation/analysis of all your views, procs and udf's.

just my 2c, keep ut the good work though

Fridthjof-G Eriksen
Thursday, 13 July 2006 12:31 AM by f_eriksen
Anonymous comments are disabled