Thursday, November 11, 2010


I'd like to welcome myself to the new century and hop on this new fangled blog bandwagon. This blog will mainly be centered around my misadventures in the world of sysadmining across a smorgasbord of platforms but I wouldn't be surprised if some tales from other genres crept into the mix.

My first post is a not so interesting story about a man, a database, and a quest to query for a listing of column names and datatypes. MySQL and several other database servers provide builtin functionality for this, but MS SQL Server makes things a little more difficult to get at. Googling for such ideas will bring you a wide array of options, but won't get exactly what you want without some massaging. I was able to start with this post but that gives you every table that is being hosted on the server. I further narrowed it down to the exact table that I wanted by performing some frankenstein experiments with a couple of other search results to come up with this:

FROM sysobjects     
     JOIN syscolumns ON =
     JOIN systypes ON syscolumns.xusertype=systypes.xusertype
WHERE = OBJECT_ID('table_of_stuff')

Pretty straight-forward but there isn't much guidance on what all the hidden goodies packed in those sys tables can actually do for you. Hope this helps someone out there out and saves a couple of minutes for you.