首页 > 资讯列表 > 编程/数据库 >> 数据库操作教程

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

数据库操作教程 2022-09-23 18:14:07 转载来源: 网络整理/侵权必删

一、查询当前部门下的所有子部门WITHdeptAS(SELECT*FROMdbo.deptTab--部门表WHEREpid=@idUNIONALLSELECTd.*FROMdbo.deptTabdINNERJOINdeptONd.pid=dept.id)SELECT*FROMdept二、查询当前部门所有上级部门WITHtabAS(SELECTDepId,ParentId,DepName,[Enable],0AS[Level]FROMdeptTabWITH(NOLOCK)--表名WHERE[Enable][email protected],b.ParentId,b.DepName,b.[Enable],a.[Level]+1FROMtaba,deptTabbWITH(NOLOCK)WHEREa.ParentId=b.depIdANDb.[enable]=1)SELECT*FROMtabWITH(NOLOCK)WHERE[enable]=1ORDERBY[level]DESC三、查询当前表的说明描述SELECTtbs.name表名,ds.value

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

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.idWHERE  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查询表结构的实例,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对脚本之家网站的支持!

标签: 节点 sqlserver 递归 sql 查询表 结构 实例


声明:本文内容来源自网络,文字、图片等素材版权属于原作者,平台转载素材出于传递更多信息,文章内容仅供参考与学习,切勿作为商业目的使用。如果侵害了您的合法权益,请您及时与我们联系,我们会在第一时间进行处理!我们尊重版权,也致力于保护版权,站搜网感谢您的分享!

站长搜索

http://www.adminso.com

Copyright @ 2007~2024 All Rights Reserved.

Powered By 站长搜索

打开手机扫描上面的二维码打开手机版


使用手机软件扫描微信二维码

关注我们可获取更多热点资讯

站长搜索目录系统技术支持