select
    a.name as column_name,
    b.name as date_type
from
    syscolumns a
join
    systypes b
    on a.xusertype=b.xusertype
where a.id=object_id('ProductClass')

------解决方案--------------------
如果只是classid下面一级,就很简单,如果是n级,

1个sql语句是无法解决问题的,可能需要配合其它的才能实现

------解决方案--------------------
SQL code


create table os(id int,parentid int,desn varchar(10))
insert into os select 1,0,'体育用品'
insert into os select 2,0,'户外运动'
insert into os select 3,1,'篮球'
insert into os select 4,1,'足球'
insert into os select 5,2,'帐篷'
insert into os select 6,2,'登山鞋'
insert into os select 7,0,'男士用品'
insert into os select 8,7,'刮胡刀'
insert into os select 9,3,'大号篮球'

--首先查找该节点下所有子接点
create function f_cid(@id int)
returns @t table(id int,parentid int,desn varchar(10),lev int)
as
begin
     declare @lev int
     set @lev=1
     insert into @t select *,@lev from  os where id=@id
     while(@@rowcount>0)
     begin
          set @lev=@lev+1
          insert into @t select a.*,@lev from os a,@t b
          where a.parentid=b.id and b.lev=@lev-1
     end
     return
end

--调用:
select * from dbo.f_cid(1)