一、查询当前部门下的所有子部门
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
二、查询当前部门所有上级部门
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
三、查询当前表的说明描述
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;
以上所述是小编给大家介绍的sql server递归子节点、父节点SQL查询表结构的实例，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对编程之家网站的支持！
原文链接：https://www.f2er.com/mssql/62692.html