前言
在数据库系统原理与设计(第3版)教科书中这样写道:
数据库包含4类数据:
1.用户数据
2.元数据
3.索引
4.应用元数据
其中,元数据也叫数据字典,定义如下:
下面这篇文章就来给大家分享一个关于查询SQL Server Table 结构的SQL 语句。
T-SQL 如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
SELECT ( case when a.colorder=1 then d. name else '' end ) 表名, a.colorder 字段序号,a. name 字段名, ( case when a.colorder=1 then isnull (f.value, '' ) else '' end ) 表说明, ( case when COLUMNPROPERTY( a.id,a. name , 'IsIdentity' )=1 then '1' else '0' 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 '1' else '0' 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 '1' else '0' 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 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 b.name is not null WHERE d. name = 'DBA_Server' --如果只查询指定表,加上此条件,此案例为表DBA_Server order by a.id,a.colorder |
查询出的结果如下:
总结
以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对服务器之家的支持。
原文链接:https://www.cnblogs.com/xuliuzai/p/9710407.html