Getting catalog information about a column

 

The SYSCOLUMNS view contains a row for each column of a table and view in the schema.

The following sample statement displays all the column names in the CORPDATA.DEPARTMENT table:

  SELECT *
    FROM CORPDATA.SYSCOLUMNS     WHERE TABLE_NAME = 'DEPARTMENT'

The result of the previous sample statement is a row of information for each column in the table. Some of the information is not visible because the width of the information is wider than the display screen.

For more information about each column, specify a select-statement like this:

  SELECT COLUMN_NAME, TABLE_NAME, DATA_TYPE, LENGTH, HAS_DEFAULT     FROM CORPDATA.SYSCOLUMNS     WHERE TABLE_NAME = 'DEPARTMENT'

In addition to the column name for each column, the select-statement shows:

• The name of the table that contains the column

• The data type of the column

• The length attribute of the column

• If the column allows default values

The result looks like this.

COLUMN_NAME	TABLE_NAME	DATA_TYPE	LENGTH	HAS_DEFAULT
DEPTNO	DEPARTMENT	CHAR	3	N
DEPTNAME	DEPARTMENT	VARCHAR	29	N
MGRNO	DEPARTMENT	CHAR	6	Y
ADMRDEPT	DEPARTMENT	CHAR	3	N

 

Parent topic:

Catalogs in database design