3 Replies Latest reply on Oct 10, 2003 7:40 PM by user8606670

    Get all the column names in a table

    406072
      How do I get a list of all the column names in a given table? I know the table name, now I want to determine the names of all the columns.

      Thanks.
        • 1. Re: Get all the column names in a table
          43385
          Query table: ALL_TAB_COLUMNS.
          • 2. Re: Get all the column names in a table
            403551
            ALL_TAB_COLUMNS
            ALL_TAB_COLUMNS describes the columns of the tables, views, and clusters accessible to the current user. To gather statistics for this view, use the SQL ANALYZE statement or the DBMS_STATS package.

            Related Views
            DBA_TAB_COLUMNS describes the columns of all tables, views, and clusters in the database.
            USER_TAB_COLUMNS describes the columns of the tables, views, and clusters owned by the current user. This view does not display the OWNER column.

            Column Datatype NULL Description
            OWNER
            VARCHAR2(30)
            NOT NULL
            Owner of the table, view, or cluster

            TABLE_NAME
            VARCHAR2(30)
            NOT NULL
            Name of the table, view, or cluster

            COLUMN_NAME
            VARCHAR2(30)
            NOT NULL
            Column name

            DATA_TYPE
            VARCHAR2(30)
            Datatype of the column

            DATA_TYPE_MOD
            VARCHAR2(3)
            Datatype modifier of the column

            DATA_TYPE_OWNER
            VARCHAR2(30)
            Owner of the datatype of the column

            DATA_LENGTH
            NUMBER
            NOT NULL
            Length of the column in bytes

            DATA_PRECISION
            NUMBER
            Decimal precision for NUMBER datatype; binary precision for FLOAT datatype, null for all other datatypes

            DATA_SCALE
            NUMBER
            Digits to right of decimal point in a number

            NULLABLE
            VARCHAR2(1)
            Specifies whether a column allows NULLs. Value is N if there is a NOT NULL constraint on the column or if the column is part of a PRIMARY KEY.

            COLUMN_ID
            NUMBER
            NOT NULL
            Sequence number of the column as created

            DEFAULT_LENGTH
            NUMBER
            Length of default value for the column

            DATA_DEFAULT
            LONG
            Default value for the column

            NUM_DISTINCT
            NUMBER
            These columns remain for backward compatibility with Oracle7. This information is now in the TAB_COL_STATISTICS views. This view now picks up these values from HIST_HEAD$ rather than COL$.

            LOW_VALUE
            RAW(32)

            HIGH_VALUE
            RAW(32)

            DENSITY
            NUMBER

            NUM_NULLS
            NUMBER
            Number of nulls in the column

            NUM_BUCKETS
            NUMBER
            The number of buckets in histogram for the column

            Note: The number of buckets in a histogram is specified in the SIZE parameter of the SQL statement ANALYZE. However, Oracle does not create a histogram with more buckets than the number of rows in the sample. Also, if the sample contains any values that are very repetitious, Oracle creates the specified number of buckets, but the value indicated by this column may be smaller because of an internal compression algorithm.

            LAST_ANALYZED
            DATE
            The date on which this column was most recently analyzed

            SAMPLE_SIZE
            The sample size used in analyzing this column

            CHARACTER_SET_NAME
            VARCHAR2(44)
            The name of the character set: CHAR_CS or NCHAR_CS

            CHAR_COL_DECL_LENGTH
            NUMBER
            The length

            GLOBAL_STATS
            VARCHAR2(3)
            For partitioned tables, indicates whether column statistics were collected for the table as a whole (YES) or were estimated from statistics on underlying partitions and subpartitions (NO).

            USER_STATS
            VARCHAR2(3)
            Were the statistics entered directly by the user?

            AVG_COL_LEN
            NUMBER
            Average length of the column (in bytes)

            CHAR_LENGTH
            NUMBER
            Displays the length of the column in characters. This value only applies to the following datatypes:

            CHAR
            VARCHAR2
            NCHAR
            NVARCHAR

            CHAR_USED
            VARCHAR2(1)
            B | C. B indicates that the column uses BYTE length semantics. C indicates that the column uses CHAR length semantics. NULL indicates the datatype is not any of the following:

            CHAR
            VARCHAR2
            NCHAR
            NVARCHAR2

            ing_joelperez@hotmail.com

            Joel P�rez
            • 3. Re: Get all the column names in a table
              user8606670
              Keep it simple. If you own the table:
              select column_name from user_tab_columns
              where table_name = 'YOUR TABLE NAME HERE';

              If you don't own the table but were granted access to it:
              select column_name from all_tab_columns
              where owner = 'TABLE OWNER HERE'
              and table_name = 'YOUR TABLE NAME HERE';