Raymond Lewallen

If you are working in Sql Server, one of the most important things you can learn is the contents of the system tables within your database. Objects are stored in sysobjects. Stored procedures are stored in syscomments. Column names for tables are stored in syscolumns by object id (just ID in the database), and there are others. Please go explore them, just don't change anything.

This came up because I was talking about pivot with someone and how a cool little keyword helps to do what takes a bunch of code in current release versions of Sql Server. I'm going to save pivot/unpivot topic for later this week. This friend-o-mine had hard coded tables that "pivoted" tables around for his purposes, with the column names of the table to pivot being hardcoded (isn't that confusing?). Now, Sql Server 2005 solves this scenario for us, as far as having to code it manually. But for those of you who are currently in 2000 or 7.0, you don't need to hardcode those column names for each row in a temp table. They already exist in the syscolumns table of your database. *NOTE* This only works well if you are actually using all the columns in the table to pivot. Outside of pivoting information, pulling this information from the syscolumns table still provides useful in many other scenarios, most commonly in data dictionary code.
Take a look at the following:
The output are the column names, in physical order as they occur in the table itself, as follows:

In a few days, I'll show you the what PIVOT and UNPIVOT do, and I'll show you the really hard and crappy way you have to do it in Sql Server 2000 and earlier (or at least the hard and crappy way I do it).