注意：表中必须存在ID与ParentID的关系

 if exists (select * from sys.objects where object_id = object_id(n'[dbo].[p_getparentallchildren]') and type in (n'p', n'pc'))
drop procedure [dbo].[p_getparentallchildren]
go

/****** object: storedprocedure [dbo].[p_getparentallchildren] script date: 12/17/2015 22:18:23 ******/
set ansi_nulls on
go

set quoted_identifier on
go

-- =============================================
-- author: mayb
-- create date: 2015-12-16
-- description: 根据父类id，获取表中所有的子类，包括子类的子类
-- =============================================
create procedure [dbo].[p_getparentallchildren]
 @tbname varchar(100),--表名
 @parentid varchar(10)--列名
as
/*

exec [p_getparentallchildren] 'tbname','1'

*/
begin
 declare @columns varchar(max) = '' --纯列名
 declare @tbcolumns varchar(max) = '' --带表前缀列名
 select ordinal_position,column_name into #temp from 数据库名.information_schema.columns where table_name=''+@tbname+''
 declare @max_ordinal_position int = (select max(ordinal_position) from #temp)
 while @max_ordinal_position > 0
 begin
 declare @split varchar(1) = ',' --列名以“,”分开，最后一个列名取消“,”
 if(@max_ordinal_position = 1)
 begin
 set @split = ''
 end
 declare @column_name varchar(50) = (select column_name from #temp where ordinal_position=@max_ordinal_position)
 set @columns += @column_name + @split
 set @tbcolumns += 'a.'+@column_name+@split
 set @max_ordinal_position = (select max(ordinal_position) from #temp where ordinal_position < @max_ordinal_position)
 end
 drop table #temp
 --构建递归查询语句
 declare @cte varchar(max) = 'with tmp as(select '+@columns+' from '+@tbname+' where parentid='+@parentid+' union all select '+@tbcolumns+' from '+@tbname+' a inner join tmp b on a.parentid = b.id ) select * from tmp'
 --print @cte 
 exec(@cte)
end

go