Information regarding keys and table information could be accessed through the sysobjects and other sys tables and I have seen some custom scripting that is capable of reading these tables to provide some of the info you're looking for here, though click drill down functionality is well out of that scope. I think TOAD (Tool for oracle app developers) might have some options along this route (though probably not in the free version).
It almost sounds like you're looking for a dashboard setup that will visualize and allow you to click and drill down through results. I'm unsure on free software for this functionality, though tools such as business objects, microstrategy, and several others will give you what (i think) you're looking for. Possibly expensive and could take quite a bit of time to implement pending your setup...tis what I get paid to do ;)
Just to echo Beth, you can create diagrams withins MSSQL...it'll give you and idea of how the tables relate, assuming proper keys and relations have been setup.
Editting to add: another comment towards this part of your questions : I already know how to view the data in a single table, and I know how to construct SQL queries that JOIN tables. However, to get N-levels deep, I have to write a SQL statement, find the ID of the item I'm interested in, and repeat N times. It is extremely tedious and hard to visualize the results.
I'll nest my queries (this is a troubleshooting technique only, don't you dare try to implement something like this for production)...
select * from finaltable where ID in
(select id2 for table2 where anotheridcolumn in
(select id3 from yetanothertable where yet anotheridcolumn in (input_id_you're_looking_for)
I really wouldn't use this technique if you expect more than 5 or 10 rows returned at the end...but it'll save some of the tedious run one statement and copy result into next work you're doing