select [表名] = CASE WHEN c.column_id = 1 then SCHEMA_NAME(t.schema_id)+ '.'+ t.name ELSE '' END,
[表创建时间] = CASE WHEN c.column_id = 1 then CONVERT(varchar,t.create_date,111) ELSE '' END,
[表修改时间] = CASE WHEN c.column_id = 1 then CONVERT(varchar,t.modify_date,111) ELSE '' END,
[表说明] = CASE WHEN c.column_id = 1 then isnull(tdesc.value,'') ELSE '' END,
[总行数] = CASE WHEN c.column_id = 1 then ISNULL((select top(1) CONVERT(varchar,idx.rows) from sysindexes idx where idx.id = t.object_id order by idx.indid),'')ELSE '' END,
[字段序号] = c.column_id,
[字段名] = c.name,
[自增标识] = CASE WHEN c.is_identity = 1 then '√' ELSE '' END,
[主键] = case when exists(
select 1 from sys.indexes idx join sys.index_columns idxC on idx.object_id = idxC.object_id and idx.index_id = idxC.index_id
where idx.object_id = t.object_id and idxC.column_id = c.column_id and idx.is_primary_key = 1
)then '√' else '' end,
[类型] = tp.name,
[占用字节数] = c.max_length,
[长度] = COLUMNPROPERTY(t.object_id, c.name,'PRECISION'),
[小数位数] = isnull(COLUMNPROPERTY(t.object_id, c.name,'Scale'),0),
[可为空] = CASE WHEN c.is_nullable = 1 then '√' ELSE '' END,
[计算列] = CASE WHEN c.is_computed = 1 then '√' ELSE '' END,
[默认值] = dc.definition, --dc.name[默认值约束]
[字段说明] = isnull(cdesc.[value],'')
from sys.tables t
inner join sysobjects o on t.object_id = o.id and o.xtype = 'U' and o.name <> 'dtproperties'
left join sys.extended_properties tdesc on t.object_id = tdesc.major_id and tdesc.minor_id = 0
inner join sys.columns c on t.object_id = c.object_id
inner join sys.types tp on c.user_type_id = tp.user_type_id
left join sys.default_constraints dc on c.default_object_id = dc.object_id
left join sys.extended_properties cdesc on t.object_id = cdesc.major_id and c.column_id = cdesc.minor_id
where t.name = 'tb_wip_DPBarcode'
order by t.object_id, c.column_id
标签:END,name,idx,column,object,server,sql,2016,id
From: https://www.cnblogs.com/wandia/p/18193575