Scenario: Being assigned to document an existing system, you need to get column level information on tables including descriptions if they exist.

Solution: The code snippet below brings back a listing of all the columns, column types and some other pertinent information for a given table. This could easily be put into a cursor to bring back information on multiple tables, or an SSIS Package or job to do some automatic documentation. (We used it while creating Word Docs about legacy systems)

Snippet:

Declare @Table Varchar(50)

Set @Table = 'TableName'

-- Get me Table Info
Select
  c.Name
  , Case
      When System_Type_Id = 56 Then 'INT'
      When System_Type_Id = 36 Then 'UniqueIdentifier'
      When System_Type_Id = 231 Then 'nVarchar (' + ')'
      When System_Type_ID = 167 Then 'Varchar(' + ')'
      When system_type_id = 175 Then 'Char (' + ')'
      When System_Type_Id = 61 Then 'DateTime'
      When System_Type_Id = 104 Then 'Bit'
      Else 'Unknown'
    End     As DataType
  , Case
      When Is_NullAble = 0 Then 'No'
      Else 'Yes'
    End     As NullAble
  , x.Value As Description
  , system_type_id
From   sys.Tables t With (NoLock)
       Join sys.Columns c With (NoLock)
         on t.Object_Id = c.Object_Id
       left outer Join ( Select
                objName
                , Value
              From   fn_ListExtendedProperty (Null, 'schema', 'dbo', 'table', @Table, 'column', Default) )x
         on c.Name = x.objName Collate DataBase_Default
Where  t.Name = @Table

-- Get me Index Info

Select
  i.Name As IndexName
, Case      When i.Type_desc = 'CLUSTERED' Then 'Yes'
      Else 'No'
    End As isClustered
  , c.Name As ColumnNames
From   sys.Tables t
       Inner Join sys.scHemAs s
         on t.scHema_Id = s.scHema_Id
       Inner Join sys.Indexes i
         on i.Object_Id = t.Object_Id
       Inner Join sys.Index_Columns ic
         on ic.Object_Id = t.Object_Id
       Inner Join sys.Columns c
         on c.Object_Id = t.Object_Id
            And ic.Column_Id = c.Column_Id
Where  i.Index_Id > 0
   And i.Type In ( 1, 2 )

   And ic.Key_Ordinal > 0
   and t.Name = @Table
Order By
  ic.Key_Ordinal