4.3 How do I…Put comments on a table and its columns?Problem
With the large number of tables used in our applications and the number of developers working in our organization, it is important that we document tables and their columns contained in the database. How do I put comments on tables and their columns?
Technique
The COMMENT statement is used to create comments on tables and columns. The comments are stored in the data dictionary and can be queried through the ALL_TAB_COMMENTS and ALL_COL_COMMENTS data dictionary views. Figure 4.4 shows the syntax of the COMMENT statement.
If the user account connected to the database is not the owner of the table, the user account must have the COMMENT ANY TABLE system privilege in order to put a comment on the table.
Steps
1. Run SQL*Plus and connect to the WAITE user account. CHP4_4.SQL, shown in Figure 4.5, creates a sample table that is used throughout this How-To.
Run the file to create the sample table.
SQL> START CHP4_4.sql
Table created.
2. Load CHP4_5.SQL into the SQL buffer. The file contains a statement to put a comment on the DEPT4 table created in the previous step.
SQL> GET CHP4_5.sql
1 COMMENT ON TABLE DEPT04 IS 2* ‘Departments in the Organization.’
The COMMENT statement is used to put a comment on either a table or a column. Line 1 presents the required keywords and identifies the DEPT04 table as the recipient of the comment. Line 2 specifies the comment placed on the table.
3. Execute the statement to create the comment.
SQL> /
Comment created.
4. Load CHP4_6.SQL into the SQL buffer. The file contains a query to view the comment created in the previous step.
SQL> GET CHP4_6.sql
1 SELECT COMMENTS
2 FROM USER_TAB_COMMENTS
3* WHERE TABLE_NAME = ‘DEPT04’
The query returns the COMMENTS column from the USER_COMMENTS data dictionary view. The WHERE clause in line 3 only returns records for the sample table created in Step 1.
5. Execute the query to view the comment created in Steps 2 and 3.
SQL> /
COMMENTS
---------------------------------------------
Departments in the Organization.
6. Load CHP4_7.SQL into the SQL buffer. The file contains a statement to create a comment on the DNAME column of the DEPT4_4 table.
SQL> GET CHP4_7.sql
1 COMMENT ON COLUMN DEPT04.DNAME IS
2 ‘The name of the department.’
Line 1 contains the COMMENT keywords and specifies the table and column name receiving the comment. Both the table and column name must be specified in the statement. If the table name is not included, the COMMENT statement will fail.
7. Execute the statement to create the comment on the column.
SQL> /
Comment created.
8. Load CHP4_8.SQL into the SQL buffer. The file contains a statement to query the comment created in Steps 6 and 7 ( see Figure 4.6).
Line 1 specifies that the COMMENTS column is returned by the query. The FROM clause in line 2 specifies the USER_COL_COMMENTS data dictionary view as the source of the query. The WHERE clause in lines 3 through 5 returns records for the DNAME column of the sample table created in Step 1.
9. Execute the statement to view the comment.
SQL> /
COMMENTS
---------------------------------------------
The name of the department
How It Works
Step 1 creates the sample table DEPT04, commented in later steps in this How-To. Steps 2 and 3 create a comment on the DEPT04 table using the COMMENT statement. The comment is stored in the COM$ table owned by the SYS user account and can be queried through the ALL_TAB_COMMENTS and USER_TAB_COMMENTS data dictionary views. Steps 4 and 5 query the comment created in Steps 2 and 3 using the USER_TAB_COMMENTS data dictionary view. Steps 6 and 7 create a comment on the DNAME column of the DEPT04 table. Column comments are also stored in the COM$ table, but are queried from the ALL_COL_COMMENTS and USER_COL_COMMENTS views. Steps 8 and 9 query the comments created using the USER_COL_COMMENTS view.
Comments
It is important to document the tables and columns in your database. Create comments on tables and columns in order to improve the maintainability of your database and applications. Table and column comments can be used within your application by querying the data dictionary views.