mysql查询数据库中所有的表结构_mssql sqlserver 查询数据库中

摘要:

下文讲述sqlserver中查询数据库下所有表结构的方法分享，如下所示:

实验环境:sql server 2008 R2

下文讲述sqlserver中使用sql脚本获取数据库下所有表结构的方法分享mssql查看数据库，如下所示：

SELECT

(case when a.colorder=1 then d.name else '' end) as [数据表名称] ,

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,'') 字段默认值

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

（编辑：源码门户网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!