Select [表名] = c.name,
[表说明] = Isnull(f.[value],''),
[列名] = a.name,
[列序号] = a.column_id,
[标识] = CASE
WHEN is_identity = 1 THEN '√'
ELSE ''
END,
[主键] = CASE
WHEN EXISTS (Select 1
FROM sys.objects x
JOIN sys.indexes y
ON x.TYPE = N'PK'
AND x.name = y.name
JOIN sysindexkeys z
ON z.id = a.object_id
AND z.indid = y.index_id
AND z.colid = a.column_id) THEN '√'
ELSE ''
END,
[类型] = b.name,
[字节数] = CASE
WHEN a.[max_length] = -1
AND b.name != 'xml' THEN 'max/2G'
WHEN b.name = 'xml' THEN '2^31-1字节/2G'
ELSE Rtrim(a.[max_length])
END,
[长度] = CASE
WHEN Columnproperty(a.object_id,a.name,'Precision') = -1 THEN '2^31-1'
ELSE Rtrim(Columnproperty(a.object_id,a.name,'Precision'))
END,
[小数] = Isnull(Columnproperty(a.object_id,a.name,'Scale'),
0),
[是否为空] = CASE
WHEN a.is_nullable = 1 THEN '√'
ELSE ''
END,
[列说明] = Isnull(e.[value],''),
[默认值] = Isnull(d.text,'')
FROM sys.columns a
LEFT JOIN sys.types b
ON a.user_type_id = b.user_type_id
INNER JOIN sys.objects c
ON a.object_id = c.object_id
AND c.TYPE = 'U'
LEFT JOIN syscomments d
ON a.default_object_id = d.id
LEFT JOIN sys.extended_properties e
ON e.major_id = c.object_id
AND e.minor_id = a.column_id
AND e.class = 1
LEFT JOIN sys.extended_properties f
ON f.major_id = c.object_id
AND f.minor_id = 0
AND f.class = 1
orDER BY c.name