您的位置:首页 > 数据库 > > 正文

sqlserver查询表结构(sql server递归子节点、父节点sql查询表结构的实例)

更多 时间:2022-01-19 00:53:07 类别:数据库 浏览量:683

sqlserver查询表结构

sql server递归子节点、父节点sql查询表结构的实例

一、查询当前部门下的所有子部门

  • WITH  dept
        AS ( SELECT  *
            FROM   dbo.deptTab --部门表
            WHERE  pid = @id
            UNION ALL
            SELECT  d.*
            FROM   dbo.deptTab d
                INNER JOIN dept ON d.pid = dept.id
           )
      SELECT *
      FROM  dept
    
  • 二、查询当前部门所有上级部门

  • WITH  tab
         AS ( SELECT  DepId ,
                ParentId ,
                DepName ,
                [Enable] ,
                0 AS [Level]
            FROM   deptTab WITH ( NOLOCK ) --表名
            WHERE  [Enable] = 1
                AND depId = @depId
            UNION ALL
            SELECT  b.DepId ,
                b.ParentId ,
                b.DepName ,
                b.[Enable] ,
                a.[Level] + 1
            FROM   tab a ,
                deptTab b WITH ( NOLOCK )
            WHERE  a.ParentId = b.depId
                AND b.[enable] = 1
           )
      SELECT *
      FROM  tab WITH ( NOLOCK )
      WHERE  [enable] = 1
      ORDER BY [level] DESC
    
  • 三、查询当前表的说明描述

  • SELECT tbs.name 表名 ,
        ds.value 描述
    FROM  sys.extended_properties ds
        LEFT JOIN sysobjects tbs ON ds.major_id = tbs.id
    WHERE  ds.minor_id = 0
        AND tbs.name = 'userTab';--表名
    
  • 四、查询当前表的表结构(字段名、属性、默认值、说明等)

  • SELECT CASE WHEN col.colorder = 1 THEN obj.name
           ELSE ''
        END AS 表名 ,
        col.colorder AS 序号 ,
        col.name AS 列名 ,
        ISNULL(ep.[value], '') AS 列说明 ,
        t.name AS 数据类型 ,
        col.length AS 长度 ,
        ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS 小数位数 ,
        CASE WHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN '√'
           ELSE ''
        END AS 标识 ,
        CASE WHEN EXISTS ( SELECT  1
                  FROM   dbo.sysindexes si
                      INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id
                                   AND si.indid = sik.indid
                      INNER JOIN dbo.syscolumns sc ON sc.id = sik.id
                                   AND sc.colid = sik.colid
                      INNER JOIN dbo.sysobjects so ON so.name = si.name
                                   AND so.xtype = 'PK'
                  WHERE  sc.id = col.id
                      AND sc.colid = col.colid ) THEN '√'
           ELSE ''
        END AS 主键 ,
        CASE WHEN col.isnullable = 1 THEN '√'
           ELSE ''
        END AS 允许空 ,
        ISNULL(comm.text, '') AS 默认值
    FROM  dbo.syscolumns col
        LEFT JOIN dbo.systypes t ON col.xtype = t.xusertype
        INNER JOIN dbo.sysobjects obj ON col.id = obj.id
                         AND obj.xtype = 'U'
                         AND obj.status >= 0
        LEFT JOIN dbo.syscomments comm ON col.cdefault = comm.id
        LEFT JOIN sys.extended_properties ep ON col.id = ep.major_id
                             AND col.colid = ep.minor_id
                             AND ep.name = 'MS_Description'
        LEFT JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id
                              AND epTwo.minor_id = 0
                              AND epTwo.name = 'MS_Description'
    WHERE  obj.name = 'userTab'--表名(点此修改) 
    ORDER BY col.colorder;
    
  • 以上所述是小编给大家介绍的sql server递归子节点、父节点sql查询表结构的实例,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对开心学习网网站的支持!

    您可能感兴趣