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:
SELECT column_name=syscolumns.name, datatype=systypes.name, length=syscolumns.length FROM sysobjects JOIN syscolumns ON sysobjects.id = syscolumns.id JOIN systypes ON syscolumns.xusertype=systypes.xusertype WHERE syscolumns.id = 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.