数据库表导出字详细描述文档

需求：

数据库表导出类似这种

 

 

实现方法

SQL查询，然后赋值到excel或者word

 

案例Sql

SELECT
字段名 = a.name,
字段说明 = isnull(g.[value],''),
类型 = CASE WHEN b.name = 'varchar' OR b.name = 'nvarchar' THEN b.name+'('+CONVERT(VARCHAR(20),COLUMNPROPERTY(a.id,a.name,'PRECISION'))+')' ELSE b.name END,
允许null值 = CASE WHEN a.isnullable ='0' THEN 'N' ELSE 'Y' end
FROM syscolumns a
left join systypes b
on a.xusertype=b.xusertype
inner join sysobjects d
on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e
on a.cdefault=e.id
left join sys.extended_properties g
on a.id=G.major_id and a.colid=g.minor_id
left join sys.extended_properties f
on d.id=f.major_id and f.minor_id=0
where d.name='HIS_request' --如果只查询指定表,加上此红色where条件，tablename是要查询的表名；去除红色where条件查询所有的表信息
order by a.id,a.colorder