Sql Sever查询表的字段信息

一、sqlserver 查询某个表的列名称、说明、备注、类型等
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
--sqlserver 查询某个表的列名称、说明、备注、类型等
SELECT
  表名 = case when a.colorder = 1 then d.name else '' end,
  表说明 = case when a.colorder = 1 then isnull(f.value, '') else '' end,
  字段序号 = a.colorder,
  字段名 = a.name,
  标识 = case when COLUMNPROPERTY(a.id, a.name, 'IsIdentity')= 1 then '√' else '' end,
  主键 = case when exists(
    SELECT
      1
    FROM
      sysobjects
    where
      xtype = 'PK'
      and parent_obj = a.id
      and name in (
        SELECT
          name
        FROM
          sysindexes
        WHERE
          indid in(
            SELECT
              indid
            FROM
              sysindexkeys
            WHERE
              id = a.id
              AND colid = a.colid
          )
      )
  ) then '√' else '' end,
  类型 = b.name,
  占用字节数 = a.length,
  长度 = COLUMNPROPERTY(a.id, a.name, 'PRECISION'),
  小数位数 = isnull(
    COLUMNPROPERTY(a.id, a.name, 'Scale'),
    0
  ),
  允许空 = case when a.isnullable = 1 then '√' else '' end,
  默认值 = isnull(e.text, ''),
  字段说明 = isnull(g.[value], '')
FROM
  syscolumns a
  left join systypes b on a.xusertype = 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
  d.name = '数据库表名' --如果只查询指定表,加上此where条件,tablename是要查询的表名;去除where条件查询所有的表信息
order by
  a.id,
  a.colorder

示例:

 

posted @   microsoft-zhcn  阅读(884)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· NetPad:一个.NET开源、跨平台的C#编辑器
· PowerShell开发游戏 · 打蜜蜂
点击右上角即可分享
微信分享提示