SQL - list columns in a table

I have gotten into the habit of using either sp_help or sp_columns to view a list of column names, for example

sp_help 'MyTable'

or

sp_columns 'MyTable'

which are much faster than using the Object Explorer in SSMS to drill-down thru the database, table and finally the columns. The downside of these sp's is that they display much more info than usually needed; I just want a list of columns & data types etc.

I created a new stored procedure wm_columns, click the link to view.

I began with sp_columns since it returns a single record set. Then, after looking in BOL I created a temp table to capture the output, and then execute the sp to populate the table:

DECLARE
@sql nvarchar(4000)

SET NOCOUNT ON

CREATE TABLE #temp
(
TABLE_QUALIFIER sysname,
TABLE_OWNER sysname,
TABLE_NAME sysname,
COLUMN_NAME sysname,
DATA_TYPE smallint,
[TYPE_NAME] sysname,
[PRECISION] int,
[LENGTH] int,
SCALE smallint,
RADIX smallint,
NULLABLE smallint,
REMARKS varchar(254),
COLUMN_DEF nvarchar(4000),
SQL_DATA_TYPE smallint,
SQL_DATETIME_SUB smallint,
CHAR_OCTET_LENGTH int,
ORDINAL_POSITION int,
IS_NULLABLE varchar(254),
SS_DATA_TYPE tinyint
)

INSERT INTO #temp
EXEC sp_columns @Table

Once that it done, it is simple to select only the columns that interest me:

SELECT
COLUMN_NAME,
[TYPE_NAME],
[PRECISION],
[LENGTH],
SCALE,
NULLABLE,
COLUMN_DEF
FROM
#temp

. . . but it would be nice to be able to sort the list alphabetically by the field name, so after populating the temp table, I create a unique index based on an argument @SortAlpha to my stored procedure:

EDIT ON 04/09/2009: There was a bug in this part of the code, the alpha sort did not work. Fixed here & also in the download file . . .

SET @sql = 'CREATE UNIQUE CLUSTERED INDEX UQ_temp ON #temp (' +
CASE @SortAlpha
WHEN 0
THEN 'ORDINAL_POSITION'
ELSE 'COLUMN_NAME'
END + ')'

. . .END OF EDIT

EXEC sp_executesql @sql

. . . which causes the ouput to be sorted according to the argument.