Select Column Name from a Table

 

Select Column Name from a Table

We know with desc table_name we can see the different attribute of the table. Someone asked me how I can select column_name from a table. It can be done through query from various tables.

DBA_TAB_COL_STATISTICS

DBA_TAB_COLUMNS                                                              ALL_TAB_COLUMNS 

DBA_TAB_COLS                                user_tab_cols 

DBA_COL_COMMENTS                                                               ALL_COL_COMMENTS 

provide column to help us to query.

Like with an example I will show this, From cols I issued query to achieve my result.

例子1：
1)SQL> create table test_table( a number, b varchar2(10),c clob);
Table created.

2)select column_name,data_type from cols where Table_name='TEST_TABLE'

COLUMN_NAME DATA_TYPE
------------------------------ --------------------
A NUMBER
B VARCHAR2
C CLOB

例子2：
Select column_name from user_tab_cols where table_name =3D'TABLE_NAME';

Remember the table name should be in capital letters.

例子3：

Try this select statement, use <<table_name>> as per your requirement: (You
can also pass it as input parameter (e.g. &table_name))

SELECT COLUMN_NAME
FROM ALL_COL_COMMENTS
WHERE TABLE_NAME LIKE <<table_name>>;
 

例子4：

 

Try like forllowing:
desc dba_tab_columns

select table_name,column_name from dba_tab_columns
where owner='SCOTT' AND TABLE_NAME='EMP';

 

例子5：

Extract the information from system table "ALL_TAB_COLUMNS" like

SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = 'EMP';

 

例子6：

describe table_name

 

DESC is a SQL*Plus command. So it is not available if you are writing SQL or PL/SQL. You have to query the data dictionary to get that information. Querying the data dictionary also allows you to handle the results programmatically. You can't really do anything with the output of the DESC command other than have a human look at it.

 

例子7：

select column_name from dba_tab_cols where table_name='aaaa' and
owner='bbbb' order by column_id;