Consultor Eletrônico

Status: Unverified

GOAL:

How To Report SQL-92 Views

FIX:

SQL-92 views cannot be reported from the Data Administration tool
(Database > Reports > PRO/SQL View).

This Solution provides a way to report SQL-92 views that do not belong
to the SYSPROGRESS schema.

The query below displays 5 fields:

  - Owner   : Owner of the view
  - Viewname: Name of the view
  - Col     : Field's name
  - Coltype : Field's data type
  - Width   : Field's width

1) Create a file called "reportViews.sql" and insert the query:

  SELECT sysviews.owner, sysviews.viewname, syscolumns.col,
         syscolumns.coltype, syscolumns.width
  FROM sysprogress.sysviews,
         sysprogress.syscolumns
  WHERE sysviews.viewname  = syscolumns.tbl AND
         sysviews.owner=syscolumns.owner AND
          sysviews.owner <> 'SYSPROGRESS'
  ORDER BY sysviews.owner,
           sysviews.viewname,
           syscolumns.id;

2) Run the SQL Explorer in order to generate the report:

    sqlexp -char -infile reportViews.sql -outfile report.txt
           -db <database name> -S <por number>  -H <host name>
           -user <user name> -password < password>

   A file "report.txt" should then be generated.