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

发布时间：2023-07-22 15:07:38 所属栏目：MsSql教程来源：

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

rush: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 * F

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

rush: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

二、查询当前部门所有上级部门

rush:sql;"> 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

三、查询当前表的说明描述

rush:sql;"> 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';--表名

四、查询当前表的表结构(字段名、属性、默认值、说明等)

= 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;

（编辑：聊城站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!