The stored procedure
CATALOG_METADATA_VIEWS returns information
of the fields of the views.
This information includes the type of the field, precision in case of
You can filter by view and/or database.
This procedure is very similar to GET_VIEW_COLUMNS and ref:CATALOG_VDP_METADATA_VIEWS, and internally work in the same way. The only difference is that they return different fields.
We recommend using the procedure
CATALOG_VDP_METADATA_VIEWS instead of this one because it provides
more information. I.e.
CATALOG_VDP_METADATA_VIEWS returns the
description of the fields and an addition field
that provides the name of type of the field.
CATALOG_METADATA_VIEWS ( input_database_name : text , input_view_name : text )
input_database_name: name of the database.
input_view_name: name of the view you want to obtain its fields.
The procedure returns a row for each field of each view.
NULL, the procedure returns the
fields of all the views of all the databases.
NULL, the procedure returns the fields of all the
views of the
The output schema has the following fields:
database_name: name of the database that the view of the field belongs to.
view_name: name of the view’s field.
view_type: type of the view:
0: base view.
1: derived view.
column_name: name of the field.
column_type_name: name of the type of the field in the “source type properties” of the field. E.g.
These are the names of the constants defined in the class java.sql.Types of the JDBC API.
column_type: number that represents the type of the field.
The values of this field are defined in the class java.sql.Types of the JDBC API. E.g.
column_type_precision: its meaning depends on the type of the field:
- For fields of type
text, it indicates the maximum length of the field.
- For numeric types, it indicates the precision. Therefore, for
longfields, this number is
- For fields of type
column_type_length: maximum length in bytes of the values of this field.
column_type_scale: number of fractional digits that a value of this field could store.
This procedure only returns information about the views on which the user has Read privileges. The implications of this are the following:
- If the user is an administrator, the procedure will return information about all the views of all the databases.
- The procedure will return information about the views of the databases on which the user is a local administrator.
- The procedure will return information about the views of the databases on which the user has Connect and Read privileges.
Note that this procedure does not return a “privileges error”. For example, let us say that:
- A user executes
CALL CATALOG_METADATA_VIEWS (null, null)(i.e. obtain information about all the fields of all the views of all databases)
- This user only has Connect and Read privileges on the database
In this scenario, the procedure only returns information about the views
testing database and not about the views of the other
SELECT view_name, column_name, column_type_name, column_type_length FROM CATALOG_METADATA_VIEWS() WHERE input_database_name='chinook' and input_view_name = 'invoice';
The result is:
The query above is equivalent to the one below; the only difference is the syntax. The syntax above is more readable because you see what each parameter mean by its name.
CALL CATALOG_METADATA_VIEWS('chinook', 'invoice');