Export the table structure of all user tables in the Oracle database

Export the table structures of all user tables in the Oracle database and store them in an Excel table. The storage information is shown in:

Export the table structures of all user tables in the Oracle database and store them in an Excel table. The storage information is shown in:

Problem description:

Export the table structures of all user tables in the Oracle database and store them in an Excel table. The storage information is shown in:

Select a. TABLE_NAME, A. COMMENTS, B. COLUMN_NAME, B. comments from USER_TAB_COMMENTS a, USER_COL_COMMENTS B WHERE a. table_name = B. table_name

Solution:

1) Start PL/SQL. log on to the target database instance by using the user who needs to export the table structure.

2) New-SQL window: Create a query window

3) enter the following query statement in the query window:

Select t. TABLE_NAME table name, t. COLUMN_ID No., t. COLUMN_NAME field name, t. DATA_TYPE, t. DATA_LENGTH, whether t. NULLABLE is null

From user_tab_columns t

User_tab_columns indicates the structure information table of the current user table.

4) click the button in the query window to display all records.

5) Right-click copy to Excel and save the query information to Excel.

6) Open the saved query result data in Excel, select data-filter, select different tables, and then retrieve the table structure of different tables.

7) if you select the table structure of a specific table during the query, execute the following statement:

Select t. TABLE_NAME table name, t. COLUMN_ID No., t. COLUMN_NAME field name, t. DATA_TYPE, t. DATA_LENGTH, whether t. NULLABLE is null

From user_tab_columns t

Where TABLE_NAME = 'table name'

Note: Replace the table name to be queried. For example, the User table is written as where TABLE_NAME = 'user'

Experience summary:

1) The table structure of all data tables of the current user is stored in the table User_tab_columns.

2) The data table all_tab_columns stores the table structure of all data tables in the current database instance.

3) The difference between the all_tab_columns table and the user_tab_columns table is that the all_tab_columns table has an owner field, which is used to store the users to which the table belongs.

You can use PL/SQL to export the query results to an Excel file.