SELECT  c.NAME AS [Column Name], t.NAME AS [Data type],

        c.max_length AS [Max Length], c.precision, c.scale,

        c.is_nullable AS [Is Nullable], c.is_identity AS [Is Identity],

        ISNULL(i.is_primary_key, 0) [Primary Key], i.type_desc,

        OBJECT_SCHEMA_NAME(fk.object_id) + '.' + OBJECT_NAME(fk.object_id)

        + ' (' + fk.Name + ')' AS [Foreign Key]

FROM    sys.columns c

        INNER JOIN sys.types t ON c.system_type_id = t.system_type_id

                                  AND c.user_type_id = t.user_type_id

        LEFT OUTER JOIN ( sys.index_columns ic

                          INNER JOIN sys.indexes i ON ic.object_id = i.object_id

                                                      AND i.is_primary_key = 1

                                                      AND ic.index_id = i.index_id

                        ) ON ic.object_id = c.object_id

                             AND ic.column_id = c.column_id

        LEFT JOIN sys.foreign_key_columns fkc ON fkc.parent_object_id = c.object_id

                                                 AND fkc.parent_column_id = c.column_id

        LEFT JOIN sys.columns fk ON fk.object_id = fkc.referenced_object_id

                                    AND fk.column_id = fkc.referenced_column_id

WHERE   c.object_id = OBJECT_ID('Table Name')

ORDER BY c.Column_ID ;