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;

以上所述是小编给大家介绍的sql server递归子节点、父节点sql查询表结构的实例，希望对大家有所帮助