Since it is a Hierarchy Table there will be n number of child or there may not be a child. The data in my question is just a sample data that may help to understand the question. n number of child data will have a level name(category) like in my example "objective, criteria, procedure, sub-procedure, etc". thus, we cannot do "ON C.ParentID = O.ID And C.Category = 'Criteria'", it should be dynamic.

Hi CHill60,
it almost solved 1 of my problem and i tested with your query
select COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE CONSTRAINT_NAME LIKE 'FK%' AND TABLE_NAME = @tname
and it returns all the fk fields not the fk of parent table it is giving error, i understood the error but dont know how to select the fk field of its parent table only not all fks.
thanks in advance....

Thanks CHill60. There is 1 problem in the solution in the where clause of@sql clause ('where id =' @id). Here i got with different name in each table for id field. so how can i get field id dynamically and use @pkID instead of 'id' in where clause. Thanks in advance.