表名,字段名,数据类型,最大长度,精度,小数位数,是否允许NULL,是否为主键,是否为自增,描述及说明
select
[tabs].[name] as [table], --表名
[cols].[name] as [name], --字段名
[typs].[name] as [type], --数据类型
[cols].[max_length] as [length], --最大长度(-1为MAX)
[cols].[precision] as [precision], --精度
[cols].[scale] as [scale], --小数位数
[cols].[is_nullable] as [is_nullable], --是否允许NULL
(
select count(1) from sys.columns c
inner join sys.index_columns ic on ic.object_id = c.object_id
and c.column_id = ic.column_id
inner join sys.indexes i on i.object_id = c.object_id
and ic.index_id = i.index_id
inner join sys.objects o on o.name = i.name
and o.type = 'PK'
where c.name = [cols].[name]
and c.object_id = [cols].[object_id]
) as [is_primary], --是否为主键
[cols].[is_identity] as [is_identity], --是否为自增
isnull([exts].[value], '') as [description] --描述、说明
from sys.columns [cols]
inner join sys.tables [tabs] on [tabs].[object_id] = [cols].[object_id]
and [tabs].[type] = 'U'
inner join sys.types [typs] on [typs].[user_type_id] = [cols].[user_type_id]
and [typs].[system_type_id] = [cols].[system_type_id]
left join sys.extended_properties [exts] on [exts].[major_id] = [cols].[object_id]
and [exts].[minor_id] = [cols].[column_id]
order by [cols].[object_id],[cols].[column_id]
