create table tree (Node int primary key, parent int)

insert into tree
select 1, null union
select 2, 1 union
select 3, 1 union
select 4, 2 union
select 5, 3 union
select 6, 4 union
select 7, 4 union
select 8, 7 union
select 9, 3 union
select 10, 4 union
select 11, 7

GO
CREATE FUNCTION AllChildren(@Parent as int = Null)
Returns @t table (Node int, [Level] int)
as
begin
	declare @notdone int;
	declare @level int;
  	insert into @t 
	select Node, 1
	from tree where (@Parent is null AND Parent is Null) OR
			(@Parent = Parent)

	set @NotDone = 1
	set @level = 2
  	while (@Notdone <> 0)
	begin
		insert into @t
		select tree.Node, @level
		from tree
		inner join @t t1
		on tree.Parent = t1.node
		where t1.level = (@Level - 1)

		set @notdone = @@rowcount
		set @level = @level + 1
	end
	return
end
GO

-- all children from the root (everyone):
select * from dbo.AllChildren(null)

-- all children of node 4:
select * from dbo.AllChildren(4)


DROP TABLE tree
drop function AllChildren