SELECT
'Table name' = case when a. colorder = 1 then d. name else' 'end,
Table description = case when a. colorder = 1 then isnull (f. value, '') else'' end,
FIELD No. = a. colorder,
Field name = a. name,
Id = case when COLUMNPROPERTY (a. id, a. name, 'isidentity ') = 1 then' √ 'else' end,
Primary Key = case when exists (SELECT 1 FROM sysobjects where xtype = 'pk' and name in (
SELECT name FROM sysindexes WHERE indid in (
SELECT indid FROM sysindexkeys WHERE id = a. id AND colid = a. colid
) Then' √ 'else' end,
Type = B. name,
Bytes occupied = a. length,
Length = COLUMNPROPERTY (a. id, a. name, 'precision '),
Decimal places = isnull (COLUMNPROPERTY (a. id, a. name, 'Scale'), 0 ),
Allow null = case when a. isnullable = 1 then '√ 'else' 'end,
Default Value = isnull (e. text ,''),
Field description = isnull (g. [value], '')
FROM syscolumns
Left join policypes B on a. xtype = B. xusertype
Inner join sysobjects d on a. id = d. id and d. xtype = 'U'
Left join syscomments e on a. cdefault = e. id
Left join sysproperties g on a. id = g. id and a. colid = g. smallid
Left join sysproperties f on d. id = f. id and f. smallid = 0
Where d. name = 'authors '-- if only the specified table is queried, add this condition.
Order by a. id, a. colorder