Reporting on the Database Schema

Tuesday 9 February 2010 @ 11:19 pm

Most databases are designed with special tables or views that are designed to store the structure of the database. Each database platform is different but the list below should be a start. To get a list of tables you can use a command object that says the following:

Oracle: SELECT table_name FROM all_tables

SQL Server: select name from sys.objects where type = ‘u’

MySQL: write a SQL command that says simply: SHOW TABLES

For more on MySQL System tables you can use this link

MS Access : SELECT * FROM MSysObjects where type = 1
1) You have to set the Acess database options to “show System tables”
2) Then in CR options check off “show system tables”.

For more on MS Access system tables you can check out this link

Older databases like early versions of Dbase, FoxPro or Btrieve store each table as a separate file in the Windows file system.  Since you can use CR to read the files in a Windows folder you can generate a list of tables using the files.  You can also report on the file size and the last time it was changed, which are not as easy in the others.

And thanks to William Chadbourne of OIT-DEP in Maine for correcting my original Oracle command.

Update 12/9/2020: Added a post with links for SQL to generate a Schema in SQL Server, Oracle or MySQL. 

Leave a Reply

Recrystallize Pro