SQL server recursive subnode, parent node SQL query table structure instance, recursive SQL

SQL server recursive subnode, parent node SQL query table structure instance, recursive SQL

1. query all sub-departments under the current Department

WITH dept AS (SELECT * FROM dbo. deptTab -- department table WHERE pid = @ id union all select d. * FROM dbo. deptTab d inner join dept ON d. pid = dept. id) SELECT * FROM dept

2. query all upper-level departments of the current Department

WITH tab AS (SELECT DepId, ParentId, DepName, [Enable], 0 AS [Level] FROM deptTab WITH (NOLOCK) -- table name WHERE [Enable] = 1 AND depId = @ depId union all select B. depId, B. parentId, B. depName, B. [Enable],. [Level] + 1 FROM tab a, deptTab B WITH (NOLOCK) WHERE. parentId = B. depId AND B. [enable] = 1) SELECT * FROM tab WITH (NOLOCK) WHERE [enable] = 1 order by [level] DESC

Iii. Description of querying the current table

SELECT tbs. name table name, ds. value description FROM sys. extended_properties ds left join sysobjects tbs ON ds. major_id = tbs. idWHERE ds. minor_id = 0 AND tbs. name = 'usertab'; -- table name

4. query the table structure of the current table (field name, attribute, default value, description, etc)

Select case when col. colorder = 1 THEN obj. name ELSE ''end AS table name, col. colorder AS No., col. name AS column name, ISNULL (ep. [value], '') AS column description, t. name AS data type, col. length AS length, ISNULL (COLUMNPROPERTY (col. id, col. name, 'Scale'), 0) AS decimal places, case when columnproperty (col. id, col. name, 'isidentity ') = 1 then' √ 'else' end as id, 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 primary key, case when col. isnullable = 1 then' √ 'else' end as can be null, ISNULL (comm. text, '') AS default value FROM dbo. syscolumns col left join dbo. policypes 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' -- table name (Click here to modify) order by col. colorder;

The above is an example of SQL server recursive subnode and parent node SQL query table structure described in the editor. I hope it will help you. If you have any questions, please leave a message for me, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!