MSSQL 获取表对应的列明，备注，字段类型

灵感来自小西装

于 2024-02-27 17:07:30 发布

阅读量460

点赞数 3

文章标签： sqlserver 数据库 mysql

本文链接：https://blog.csdn.net/qinggan1/article/details/136327321

版权

本文详细描述了如何通过编写SQL查询获取特定表的列信息，为开发自动代码生成工具提供基础数据，提高效率。

摘要由CSDN通过智能技术生成

旧系统代码CV多了想解放一下双手写个代码生成器，这时候就需要获取到表的某一些信息了

SELECT 
 a.NAME AS colname,
 CONCAT(UPPER(SUBSTRING(b.name, 1, 1)), LOWER(SUBSTRING(b.name, 2,LEN(b.name)-1))) AS typename,
 a.length AS length,
 a.scale AS scale, a.prec AS prec, c.name,isnull(f.[value],'') as remark
FROM syscolumns a
inner join systypes b on a.xusertype = b.xusertype
inner join sysobjects c on a.id = c.id 
inner join 
sys.columns d on d.name=a.name
inner join
sys.objects e on d.object_id=e.object_id and e.Type='U'
left join
sys.extended_properties f on f.major_id=e.object_id and f.minor_id=d.Column_id and f.class=1
where  c.name = 'tableName' and e.name='tableName'
order by a.colorder asc