sql server递归子节点，父节点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

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;