I started a project to generate stored procedures for SQL Server from a table definition.  To do that I have to be able to get a column list for a table.  INFORMATION_SCHEMA.COLUMNS does provide a lot of data, but not all I need.  I also need to know if a column is part of a primary key.  In addition I need to know if a column is a timestamp column that can be used for optimistic concurrency handling.  Timestamp and RowVersion types are used interchangeably in SQL Server.  Most people should be using RowVersion, but it is not even available in SSMS still.  Hence, it appears that timestamp is still more popular.  For some types, such as character based I also need to know the maximum length, which is available in columns view.  I decided to use sys.* system views to get the data.  The main one is sys.columns.  Here is the final query I came up with.

Select
     sys.tables.name As TableName,
     sys.columns.name As ColumnName,
     sys.schemas.name As SchemaName,
     sys.types.name As TypeName,
     sys.columns.max_length As MaximumLength,
     sys.columns.precision As ColumnPrecision,
     sys.columns.scale As ColumnScale,
     sys.columns.column_id As OrdinalPosition,
     Upper(sys.types.name) +
     Case
         When sys.types.name LIKE ‘%TEXT’ OR sys.types.name IN (‘IMAGE’, ‘SQL_VARIANT’ ,’XML’) Then ”
         When sys.types.name LIKE ‘%CHAR’ AND sys.columns.max_length = -1 Then ‘(MAX)’
         When sys.types.name LIKE ‘%CHAR’ AND sys.columns.max_length > -1 Then ‘(‘ + CAST(sys.columns.max_length As nvarchar(11)) + ‘)’
         When sys.types.name LIKE ‘%BINARY’ AND sys.columns.max_length = -1 Then ‘(MAX)’
         When sys.types.name LIKE ‘%BINARY’ AND sys.columns.max_length > -1 Then  ‘(‘ + CAST(sys.columns.max_length As nvarchar(11)) + ‘)’
         When sys.types.name IN (‘DECIMAL’, ‘NUMERIC’) Then  ‘(‘ + CAST(sys.columns.precision AS nvarchar(11)) + ‘,’ + CAST(sys.columns.scale As nvarchar(11)) + ‘)’
         When sys.types.name IN (‘FLOAT’) Then ‘(‘ + CAST(COALESCE(sys.columns.precision, 18) AS varchar(11)) + ‘)’
         When sys.types.name IN (‘DATETIME2’, ‘DATETIMEOFFSET’, ‘TIME’) Then  ‘(‘ + CAST(COALESCE(sys.columns.scale, 7) AS varchar(11)) + ‘)’
         Else ”
     End AS ColumnFullType,
     sys.columns.is_computed as Computed,
     sys.columns.is_identity as IdentityColumn,
     sys.columns.is_nullable as Nullable,
     Coalesce((Select sys.index_columns.key_ordinal
         From sys.index_columns
         Inner Join sys.indexes on sys.indexes.object_id = sys.index_columns.object_id and sys.indexes.index_id = sys.index_columns.index_id
         Where sys.index_columns.object_id = sys.columns.object_id and sys.index_columns.column_id = sys.columns.column_id and sys.indexes.is_primary_key=1), 0) As PrimaryKeyColumnPosition,
     Case When sys.types.name = ‘timestamp’ Then Cast(1 as Bit) Else Cast(0 as Bit) End As IsRowVersion
From sys.columns
Inner Join sys.tables On sys.tables.object_id = sys.columns.object_id
Inner Join sys.schemas On sys.schemas.schema_id = sys.tables.schema_id
Inner Join sys.types On sys.types.user_type_id = sys.columns.user_type_id
Where sys.tables.name=’Person’
Order By sys.columns.column_id

I am using sys.index_columns view to get an ordinal position of a column in a primary key.  Zero means that a column in question is not part of a primary key..  I am also using sys.schemas and sys.tables to get schema and table names.

Enjoy.