How can I explore the data in a SQL database, including foreign tables?

I want to know if any tools exist to explore the data in a relational database, and开发者_C百科 to drill through master-detail relationships.

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.

However, I want to know if there is a tool that lets me look at the data in a table, and if there are foreign keys, lets me expand the data to show the foreign data. And hopefully, lets me drill through multiple levels of detail.

Do any such tools exist?

I'm using MS SQL, and using SQL Server Management Studio to execute SQL.

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

Would creating a diagram help? Or using the visual part of a view?