一、查詢當前部門下的所有子部門
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查詢表結構的實例，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復大家的。在此也非常感謝大家對腳本之家網站的支持！
您可能感興趣的文章:- SQL Server 樹形表非循環遞歸查詢的實例詳解
- 使用SqlServer CTE遞歸查詢處理樹、圖和層次結構
- 使用SQLSERVER 2005/2008 遞歸CTE查詢樹型結構的方法
- SQLSERVER2005 中樹形數據的遞歸查詢
- SQLserver2008使用表達式遞歸查詢
- 高效的SQLSERVER分頁查詢(推薦)
- SQL Server SQL高級查詢語句小結
- Sql server2005 優化查詢速度50個方法小結
- SQLserver 實現分組統計查詢（按月、小時分組）
- sqlserver 模糊查詢常用方法
- sql server實現遞歸查詢的方法示例
