/*

       04/04/2009

       can use instead of sp_help or sp_columns for tables

              to quickly view a list of columns & data types

       default sort is by ordinal column position;

              optional 2nd parameter allows sort by alpha

*/

IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('dbo.wm_columns') AND type = 'P')

DROP PROC dbo.wm_columns

GO

 

CREATE PROC dbo.wm_columns

       (

       @Table sysname,

       @SortAlpha bit = 0

       )

AS

 

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

 

SET @sql = 'CREATE UNIQUE CLUSTERED INDEX UQ_temp ON #temp (' +

       CASE @SortAlpha

              WHEN 0

              THEN 'ORDINAL_POSITION'

              ELSE 'COLUMN_NAME'

       END + ')'

 

EXEC sp_executesql @sql

 

SELECT

       COLUMN_NAME,

       [TYPE_NAME],

       [PRECISION],

       [LENGTH],

       SCALE,

       NULLABLE,

       COLUMN_DEF

FROM

       #temp

 

DROP TABLE #temp

GO