I have gotten into the habit of using either sp_help or sp_columns to view a list of column names, for example
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:
SET NOCOUNT ON
CREATE TABLE #temp
INSERT INTO #temp
EXEC sp_columns @Table
Once that it done, it is simple to select only the columns that interest me:
. . . 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 (' +
END + ')'
. . .END OF EDIT
EXEC sp_executesql @sql
. . . which causes the ouput to be sorted according to the argument.