Oracle query data table structure (field, type, size, remarks)

The following SQL statement is used to create a table: createtableTABLE_TEST (NAMEvarchar2 (40) notnull, SEXvarchar2 (1) defaultYnotnull, role, HEIGHTnumber (), WEIGHTnumber (), MEMOblob ); -- add commentoncol to the column

The SQL statement for creating a table is as follows: create table TABLE_TEST (NAME varchar2 (40) not null, SEX varchar2 (1) default ''' 'y' ''' not null, BIRTHDAY date not null, HEIGHT number (3, 2), WEIGHT number (3, 2), MEMO blob); -- add comment on col to the column

The SQL statement for creating a table is as follows:
-- Create a data table
Create table TABLE_TEST
(
NAME varchar2 (40) not null,
SEX varchar2 (1) default ''' Y ''' not null,
BIRTHDAY date not null,
HEIGHT number (3, 2 ),
WEIGHT number (3, 2 ),
MEMO blob
);
-- Add remarks to columns
Comment on column TABLE_TEST.NAME is ''' name '''';
Comment on column TABLE_TEST.SEX is ''' gender '''';
Comment on column TABLE_TEST.BIRTHDAY is ''' 'birthday '''';
Comment on column TABLE_TEST.HEIGHT is '''height '''';
Comment on column TABLE_TEST.WEIGHT is ''' weight '''';
Comment on column TABLE_TEST.MEMO is ''' remarks '''';
-- Create a foreign key of the primary key of the constraint relationship.
Alter table TABLE_TEST add constraint TB_TEST_P_NAME primary key (NAME );

After the data table is created, run the following SQL statement:
Select
A. column_name field name, A. data_type data type, A. data_length length, A. data_precision integer,
A. Data_Scale decimal point, A. nullable allows null values, A. Data_default default value, B. comments remarks
From
User_tab_columns A, user_col_comments B
Where
A. Table_Name = B. Table_Name
And A. Column_Name = B. Column_Name
And A. Table_Name = ''' TABLE _ test ''''

We can get the following results:

Field name	Data Type	Length	Integer	Decimal places	Null value allowed	Default Value	Remarks
NAME	VARCHAR2	40			N		Name
SEX	VARCHAR2	1			N		Gender
BIRTHDAY	DATE	7			N		Birthday
HEIGHT	NUMBER	22	3	2	Y		Height
WEIGHT	NUMBER	22	3	2	Y		Weight
MEMO	BLOB	4000			Y		Remarks

In this way, when designing a program, we can use a simple SQL statement to call Word through Ole to export the complete database table dictionary document for the end user.
Run the following SQL statement:
Select
INDEX_NAME index name, INDEX_TYPE index type, and UNIQUENESS index category
From
User_indexes
Where
TABLE_NAME = ''' TABLE _ test ''''

The result is as follows (Note: SYS_IL0000031226C00006 $ the index is automatically created when the system creates a database table for the maintenance of the database table content ):

	Index name	Index type	Index category
1	SYS_IL0000031226C00006$$	LOB	UNIQUE
2	TB_TEST_P_NAME	NORMAL	UNIQUE

　

Run the following SQL statement to obtain more information about the database table structure:
Select
A. column_name field name, A. data_type data type, A. data_length length, A. data_precision integer,
A. Data_Scale decimal point, A. nullable allows null values, A. Data_default default value, B. comments remarks,
C. IndexCount index times
From
User_tab_columns,
User_col_comments B,
(Select count (*) IndexCount, Column_Name from User_Ind_Columns where Table_Name = ''' TABLE _ test''' 'group by Column_Name) C
Where
A. Table_Name = B. Table_Name
And A. Column_Name = B. Column_Name
And A. Column_Name = C. Column_Name (+)
And A. Table_Name = ''' TABLE _ test ''''

The result is as follows:

Field name	Data Type	Length	Integer	Decimal places	Null value allowed	Default Value	Remarks	Index count
BIRTHDAY	DATE	7			N		Birthday
HEIGHT	NUMBER	22	3	2	Y		Height
MEMO	BLOB	4000			Y		Remarks
NAME	VARCHAR2	40			N		Name	1
SEX	VARCHAR2	1			N		Gender
WEIGHT	NUMBER	22	3	2	Y		Weight

　

Of course, the application of Oracle Data Dictionary is far more than that. With the support of Oracle Database dictionary, We can get all the information about the Oracle database structure, the famous database development tool PL/SQL Developer is fully implemented based on the Oracle database dictionary.