Find more info about database

Question

I inherited a bugzilla-like page that allows users to look at the bug list. I'd like to extend it so it can be possible to answer and close them.

The problem is I don't know much about the database structure. All I have is connection details and few sample sql queries, which gives good hints about few tables. How can I find out the database type (don't even know that) and get table list and detailed info about them?

Answer 1

You'll want to investigate the database engine's data dictionary, if it has one. It all depends upon what kind of database it is. To investigate a bit, read on!

• For Oracle, there's a huge set of tables that form the data dictionary. Try running:

  SELECT * FROM SYS.ALL_TABLES; -- To figure out tables

  SELECT * FROM SYS.ALL_TAB_COLUMNS WHERE owner = 'schema' and table_name = 'table_name_found_above';

• For SQLite, try running (although I doubt a bugzilla is running this):

  SELECT * FROM sqlite_master; -- this should list all column names in the CREATE statement

• In MySQL, PostgreSQL, SQL Server try running:

  SELECT * FROM INFORMATION_SCHEMA.tables;

  SELECT * from INFORMATION_SCHEMA.columns WHERE table_schema = 'schema' and table_name = 'table';

• In DB2, try:

  SELECT * FROM SYSCAT.tables;

  SELECT * FROM SYSCAT.columns where tabname = 'table' and tabschema = 'schema';

If you don't know what database engine it's running on, just try running a bunch of the table queries in your query tool until they start returning valid results; it'll help you narrow down which db engine your'e running. You can then substitute the table names in for the column queries.

The above options should help you whittle it down. From there, you can pull up the database engine's documentation and learn more about it's features. Either that, or I'd go talk with the server admin who runs the bug tracker you now own, and see if he/she can dig up more info.

Answer 2

I'm presuming you have a working app that uses this database. If it connects through ODBC, you can (in Windows at least) turn on ODBC logging. They're painful to manually parse through, but that can be an additional source of information.

Once you know what type of database you're looking at, fire up your query tool of choice, and start digging around.