Print Tree

create procedure [dbo].[DBA_PrintTree] (@root int)
as
set nocount on;

with btree (treeid,parenttreeid,nodename,level)
as
(
select treeid,parenttreeid,nodename,0 as level from tree
where treeid=@root
union all
select b1.treeid,b1.parenttreeid,b1.nodename,level+1  from tree b1 join btree b2 on b2.treeid=b1.parenttreeid 
)

select treeid,parenttreeid,nodename,level into #t from btree order by level, parenttreeid


declare @stack table(d int identity(1,1) primary key,node int)
declare @t table(treeid int,parenttreeid int,nodename varchar(64),level int)
declare @parent int,@nodename varchar(64),@level int,
@bid int,@bparent int
set @parent=@root
insert @t select * from #t where treeid =@root
select @nodename=nodename from @t
print @nodename
delete #t where treeid =@root
insert @stack values(@root)


while exists(select top 1 node from @stack)
begin
while exists (select treeid from #t where parenttreeid=@parent)
begin
select top 1 @bid=treeid,@bparent=parenttreeid,@nodename=nodename,@level=level from #t 
where parenttreeid=@parent order by nodename
insert @t values(@bid,@bparent,@nodename,@level)
print space(@level*5)+@nodename
delete #t where treeid=@bid
insert @stack values(@bid)
set @parent=@bid
end
delete @stack where d in (select max(d) from @stack)
select top 1 @parent=node from @stack order by d desc
end

select * from @t
drop table #t