一、查询当前部门下的所有子部门
1
2
3
4
5
6
7
8
9
10
11
|
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
|
二、查询当前部门所有上级部门
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
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
|
三、查询当前表的说明描述
1
2
3
4
5
6
|
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' ; --表名
|
四、查询当前表的表结构(字段名、属性、默认值、说明等)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
|
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查询表结构的实例,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对服务器之家网站的支持!
原文链接:http://www.cnblogs.com/WhyShang/archive/2017/02/23/6435080.html