SQL System Catalog: View Information

The definitions of the views in a database are usually stored by the DBMS in the system catalog. The DB2 catalog contains two system tables that keep track of views. The SYSCAT.VIEWS table, described in Table 16-6, contains the SQL text definition of each view. If the definition exceeds 3600 characters, it is stored in multiple rows, with sequence numbers 1, 2, 3, and so on.

The DB2 SYSCAT.VIEWDEP table, described in Table 16-7, shows how each view depends on other tables or views. There is one row in the table for each dependency, so a view with three source tables will be represented by three rows.

Using these two tables, you can see the definitions of the views in the database and quickly determine which tables in the database serve as the source tables for a view. As with many mainstream DBMS products, information about views is tightly linked to the information about tables in the DB2 catalog. This means there is often more than one way to find the answer to a catalog inquiry. For example, here is a direct query against the DB2 VIEWS system table to obtain the names and creators of all views defined in the database:

List the views defined in the database.

SELECT DISTINCT VIEWSCHAME, VIEWNAME, DEFINER

  FROM SYSCAT.VIEWS

Note the use of DISTINCT to eliminate duplicate rows that would be present for views with long text definitions. Perhaps an easier way to obtain the same information is to query the DB2 TABLES system table directly, selecting only rows representing views, as indicated by the TYPE value:

List the views defined in the database.

 SELECT TABSCHEMA, TABNAME, DEFINER

   FROM SYSCAT.TABLES

  WHERE TYPE = ‘V’

Most of the major DBMS products treat views in this same way within their system catalog structure. Informix Universal Server, for example, has a system table named SYSVIEWS that contains view definitions. Each of its rows holds a 64-character chunk of the SQL SELECT statement that defines a view. View definitions that span multiple rows are handled by sequence numbers, as with DB2. The Informix SYSVIEWS table includes only one other column—the table-id that links the SYSVIEWS table to the corresponding row in the SYSTABLES table. Thus, Informix duplicates less information between the SYSTABLES and SYSVIEWS tables, but you must explicitly join the tables for the most common view information requests.

Oracle takes a similar approach by making the SQL text that defines a view available via system views. As with table and column information, there are two system views of interest: USER_VIEWS, which contains information about all views created and owned by the current user, and ALL_VIEWS, which also includes information about views accessible to the current user but created by other users. Unlike the DB2 and Informix approaches, which split the SQL text defining the view into multiple rows with sequence numbers if it is lengthy, Oracle’s system views contain only one row per view. The SQL text defining the view is held in a LONG (large object) column and can conceivably run to thousands of characters. A length column tells the length of the stored SQL text definition of the view. Here is a query to obtain Oracle view information:

List the current user’s views and their definitions.

 SELECT VIEW_NAME, TEXT_LENGTH, TEXT

   FROM USER_VIEWS

Note that most interactive SQL products (including Oracle’s) truncate the text containing the view definition if it is too long to be displayed effectively. The actual text stored in the database is complete.

Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.

Leave a Reply

Your email address will not be published. Required fields are marked *