mysql查询数据库中所有的表结构_mssql sqlserver 查询数据库中所有表结构的方法分享...

摘要:

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

实验环境:sql server 2008 R2

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

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