Printing a Table Structure

Creating tables and queries

Printing a Table Structure

Postby LJ Bettona » Fri Feb 22, 2008 12:21 pm

I have just finished creating my first database. Yippee!! Now, I want to print out the table structures for my records so I will remember what I did and make notes on them about why, etc. Only, I cannot find a print command anywhere. How do I do this? I know I used to have to go through hell to get programmers to document their work, for heavens sake, don't make it any more difficult for project managers, please!

At the moment I've 'grabbed' the windows and created a document, but that isn't an answer, just a work 'round.

Do I have to create some sort of report?

As always, thanks for any help.
iMac G5, OS 10.4.11
OO 2.4, NeoOffice 3 patch 1
OOo 2.2.X on Mac OSx other + Actually NeoOffice 3 patch 1
User avatar
LJ Bettona
 
Posts: 58
Joined: Fri Jan 04, 2008 12:31 pm
Location: Italy

Re: Printing a Table Structure

Postby Sliderule » Fri Feb 22, 2008 11:39 pm

LJ Bettona:

You have asked, a great question:
LJ Bettona wrote:Now, I want to print out the table structures for my records so I will remember what I did and make notes on them about why, etc.

This is how I do it. I am assuming, you are using the OpenOffice Embedded Database, HSQL. You can confirm this by looking at the 'status bar' at the bottom of the screen, after opening your database.

To provide the information you want . . . follow these steps:

  1. Open your database
  2. Under the Database column, click on Queries
  3. Under Tasks, click on Create Query in SQL View...
  4. Copy the following SQL statement
    Code: Select all   Expand viewCollapse view
    SELECT
       A.table_type,  -- TABLE or VIEW
       A.hsqldb_type,   -- CACHED or TEXT
       A.table_name,
       B.column_name,
       B.type_name,
       B.column_size

    FROM information_schema.system_tables as A, information_schema.system_columns as B
    --  information_schema.system_indexinfo  is index information

    WHERE A.table_schem = 'PUBLIC'
       AND A.table_name = B.table_name

    ORDER by A.TABLE_TYPE, A.table_name, B.ordinal_position
  5. Press the Run SQL command directly ICON, OR From the menu: Edit -> Run SQL command directly ( so it has a Check Mark )
  6. Press the Run Query ICON, OR Press F5
  7. Save your Query ( I named mine as: information_schema_table_col )
  8. For similar information about the indices in your database . . . the following SQL will work:
    Code: Select all   Expand viewCollapse view
    -- Index information from all tables in database

    SELECT
       A.table_type, 
       A.table_name,
       B.index_name,
       B.column_name,
       B.ordinal_position,
       B.asc_or_desc,
       B.non_unique

    FROM information_schema.system_tables as A,  information_schema.system_indexinfo as B

    WHERE A.table_schem = 'PUBLIC'
       AND A.table_name = B.table_name

    ORDER by A.table_type, A.table_name, B.index_name, B.ordinal_position
  9. Press the Run SQL command directly ICON, OR From the menu: Edit -> Run SQL command directly ( so it has a Check Mark )
  10. Save your Query ( I named mine as: information_schema_table_index )

Now, you have 'documentation' . . . for your Database structure. With these Queries,
  • Create a Report using Sun Report Tool
  • Open Calc Spreadsheet, Press F4 to open the saved query(s) in your database, and, Copy the data to Calc . . . your Spreadsheet. You may then add comments about column information etc, and, Print the data . . . from within Calc, as you see fit.

I hope this helps :ugeek: , please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1162
Joined: Thu Nov 29, 2007 9:46 am


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 3 guests