Retrieving column information with description.

• Antonio Macedo

  SSC Enthusiast

  Points: 117

  More actions

  June 29, 2006 at 8:30 am

  #191127

  Hello,

  Here is my solution to this task.

  select

  C.Table_Name

  , C.Column_Name

  , C.Data_Type

  , (select value

  from fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', C.Table_Name, 'column', C.Column_Name)

  ) as Description

  from

  information_schema.columns as C

  Does anyone have a better way of doing this?

  Thanks

  Antonio Macedo

   

   

• July 3, 2006 at 8:00 am

  This was removed by the editor as SPAM

• Sameer Raval

  Hall of Fame

  Points: 3214

  More actions

  July 3, 2006 at 9:07 am

  #647241

  Try this one

  SELECT ORDINAL_POSITION AS POSITION,

  COLUMN_NAME = Left(COLUMN_NAME, 20),

  DATA_TYPE = Left(DATA_TYPE, 10),

  CHARACTER_MAXIMUM_LENGTH as MAX_LEN,

  COLUMN_DEFAULT ,IS_NULLABLE AS ISNULL,

  NUMERIC_PRECISION AS NUM_PRECISION,

  NUMERIC_SCALE AS NUM_SCALE

  FROM  Master.Information_Schema.Columns

  WHERE Table_Name = 'test'                 ---Change name to your table name

  Hope this helps,

   

  Kindest Regards,

  Sameer Raval [Sql Server DBA]
  Geico Insurance
  RavalSameer@hotmail.com