SELECT
(case when a.colorder=1 then d.name else '' end) 表名,
a.colorder 字段序号,
a.name 字段名,
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识,
(case when (SELECT count(*)
FROM sysobjects
WHERE (name in (SELECT name
FROM sysindexes
WHERE (id = a.id) AND (indid in (SELECT indid
FROM sysindexkeys
WHERE (id = a.id) AND (colid in (SELECT colid
FROM syscolumns
WHERE (id = a.id) AND (name = a.name)
)
)
)
)
)
) AND (xtype = 'PK')
) > 0 then '√' else '' end) 主键,
b.name 类型,
a.length 占用字节数,
COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,
(case when a.isnullable=1 then '√'else '' end) 允许空,
isnull(e.text,'') 默认值,
isnull(g.[value],'') AS 字段说明 FROM syscolumns a
left join systypes b on a.xtype=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 sysproperties g on a.id=g.id AND a.colid = g.smallid
取数据库所有表及结构
原创
©著作权归作者所有:来自51CTO博客作者jack2022的原创作品,请联系作者获取转载授权,否则将追究法律责任
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
「从ES到CK 04」Clickhouse表引擎选择和表结构设计
介绍日志平台的clickhouse表引擎选择和表结构设计
表结构 clickhouse ck 日志平台 -
MySQL表结构导出Excel、导出Word
源作者该工具主要用于导出excel、word,方便快速编写《数据库设计文档》
mysql sql jar java 运行方式