Alphabetical list of views

Chapter 51. SQL Anywhere System Views
Alphabetical list of views

Each of the views is described by its CREATE statement.

SYS.SYSCATALOG

     CREATE VIEW SYS.SYSCATALOG ( creator,
         tname, dbspacename, tabletype, ncols,
         primary_key, "check", remarks )
     AS
     SELECT ( SELECT user_name FROM SYS.SYSUSERPERM
                     WHERE user_id = SYSTABLE.creator ),
             table_name,
             ( SELECT dbspace_name from SYS.SYSFILE
                     WHERE file_id = SYSTABLE.file_id ),
             IF table_type='BASE' THEN 'TABLE'
                 ELSE table_type ENDIF,
             ( SELECT count(*) FROM SYS.SYSCOLUMN
                 WHERE table_id = SYSTABLE.table_id ),
             IF primary_root = 0 THEN 'N' ELSE 'Y' ENDIF,
             IF table_type <> VIEW' THEN view_def ENDIF,
             remarks
     FROM SYS.SYSTABLE

Lists all the tables and views from SYSTABLE in a readable format.

SYS.SYSCOLAUTH

     CREATE VIEW SYS.SYSCOLAUTH ( grantor, grantee,
         creator, tname, colname )
     AS
     SELECT ( SELECT user_name FROM SYS.SYSUSERPERM
                 WHERE user_id = SYSCOLPERM.grantor ),
             ( SELECT user_name FROM SYS.SYSUSERPERM
                 WHERE user_id = SYSCOLPERM.grantee ),
             ( SELECT user_name
                 FROM SYS.SYSUSERPERM == SYS.SYSTABLE
                 WHERE table_id = SYSCOLPERM.table_id ),
             ( SELECT table_name FROM SYS.SYSTABLE
                 WHERE table_id = SYSCOLPERM.table_id ),
             ( SELECT column_name FROM SYS.SYSCOLUMN
                 WHERE table_id = SYSCOLPERM.table_id
                 AND column_id = SYSCOLPERM.column_id )
     FROM SYS.SYSCOLPERM

Presents column update permission information in SYSCOLPERM in a more readable format.

SYS.SYSCOLUMNS

     CREATE VIEW SYS.SYSCOLUMNS ( creator, cname, tname,
     coltype, nulls, length, syslength,
     in_primary_key, "colno", default_value, remarks )
     AS
     SELECT ( SELECT user_name FROM SYS.SYSUSERPERM
                 WHERE user_id = SYSTABLE.creator ),
             column_name, table_name,
             ( SELECT domain_name FROM SYS.SYSDOMAIN
                 WHERE domain_id = SYSCOLUMN.domain_id ),
             nulls, width, scale, pkey, column_id,
             "default", SYSCOLUMN.remarks
     FROM SYS.SYSCOLUMN == SYS.SYSTABLE

Presents a readable version of the table SYSCOLUMN. (Note the S at the end of the view name that distinguishes it from the SYSCOLUMN table.)

SYS.SYSFOREIGNKEYS

     CREATE VIEW SYS.SYSFOREIGNKEYS ( foreign_creator,
         foreign_tname, primary_creator,
         primary_tname, role, columns )
     AS
     SELECT ( SELECT user_name FROM
                 SYS.SYSUSERPERM == SYS.SYSTABLE
                 WHERE table_id = foreign_table_id ),
             ( SELECT table_name FROM SYS.SYSTABLE
                 WHERE table_id = foreign_table_id ),
             ( SELECT user_name
                 FROM SYS.SYSUSERPERM == SYS.SYSTABLE
                 WHERE table_id = primary_table_id ),
             ( SELECT table_name FROM SYS.SYSTABLE
                 WHERE table_id = primary_table_id ), role,
             ( SELECT list( string( FK.column_name,
                 ' IS ', PK.column_name ) )
                 FROM SYS.SYSFKCOL KEY JOIN
                 SYS.SYSCOLUMN FK, SYS.SYSCOLUMN PK
                 WHERE foreign_table_id =
                 SYSFOREIGNKEY.foreign_table_id
                 AND foreign_key_id =
                          SYSFOREIGNKEY.foreign_key_id
                 AND PK.table_id =
                         SYSFOREIGNKEY.primary_table_id
                 AND PK.column_id =
                         SYSFKCOL.primary_column_id )
     FROM SYS.SYSFOREIGNKEY

Presents foreign key information from SYSFOREIGNKEY and SYSFKCOL in a more readable format.

SYS.SYSGROUPS

     CREATE VIEW SYS.SYSGROUPS ( group_name, member_name )
     AS
     SELECT g.user_name, u.user_name
     FROM    SYS.SYSGROUP,
             SYS.SYSUSERPERM g,
             SYS.SYSUSERPERM u
     WHERE group_id = g.user_id
     AND group_member = u.user_id

Presents group information from SYSGROUP in a more readable format.

SYS.SYSINDEXES

     CREATE VIEW SYS.SYSINDEXES ( icreator, iname, fname, creator,
     tname, indextype, colnames, interval, level )
     AS
     SELECT ( SELECT user_name FROM SYS.SYSUSERPERM
     WHERE user_id = SYSINDEX.creator ),
     index_name,
     ( SELECT file_name FROM SYS.SYSFILE
     WHERE file_id = SYSINDEX.file_id ),
     ( SELECT user_name FROM SYS.SYSUSERPERM
     WHERE user_id = SYSINDEX.creator ),
     table_name,
     IF "unique" = 'Y' THEN 'Unique'
     ELSE 'Non-unique' ENDIF,
     ( SELECT list( string( column_name,
     IF "order" = 'A' THEN ' ASC' i
     ELSE ' DESC' ENDIF ) )
     FROM SYS.SYSIXCOL == SYS.SYSCOLUMN
     WHERE index_id = SYSINDEX.index_id ), 0, 0
     FROM SYS.SYSTABLE KEY JOIN SYS.SYSINDEX

Presents index information from SYSINDEX and SYSIXCOL in a more readable format.

SYS.SYSOPTIONS

     CREATE VIEW SYS.SYSOPTIONS ( user_name, "option", "setting" )
     AS
     SELECT ( SELECT user_name FROM SYS.SYSUSERPERM
                 WHERE user_id = SYSOPTION.user_id ),
                 "option", "setting"
     FROM SYS.SYSOPTION

Displays option settings contained in the table SYSOPTION in a more readable format.

SYS.SYSPROCPARMS

     CREATE VIEW SYS.SYSPROCPARMS ( creator, parmname, procname,
     parmtype, parmmode, parmdomain, length, remarks )
     AS
     SELECT ( SELECT user_name FROM SYS.SYSUSERPERM
                 WHERE user_id = SYSPROCEDURE.creator ),
             parm_name, proc_name, parm_type,
             IF parm_mode_in = 'Y' AND
                 parm_mode_out = 'N' THEN 'IN'
             ELSE IF parm_mode_in = 'N'
                 AND parm_mode_out = 'Y' THEN 'OUT'
             ELSE 'INOUT' ENDIF ENDIF,
             ( SELECT domain_name FROM SYS.SYSDOMAIN
                 WHERE domain_id = SYSPROCPARM.domain_id ),
             width, SYSPROCPARM.remarks
     FROM SYS.SYSPROCPARM == SYS.SYSPROCEDURE

Lists all the procedure parameters from SYSPROCPARM in a readable format.

SYS.SYSREMOTEUSERS

     CREATE VIEW SYS.SYSREMOTEUSERS
     AS SELECT(SELECT user_name FROM SYS.SYSUSERPERM AS u
     WHERE u.user_id=r.user_id) AS user_name,
     "consolidate",
     (SELECT type_name FROM SYS.SYSREMOTETYPE AS t
     WHERE t.type_id=r.type_id) AS type_name,
     "address",frequency,send_time,
     (IF frequency='A' THEN
     NULL
     ELSE
     IF frequency='P' THEN
     IF time_sent IS NULL THEN
     current timestamp
     ELSE
     (SELECT min(minutes(time_sent,
     60*hour(a.send_time)
     +minute(seconds(a.send_time,59))))
     FROM SYS.SYSREMOTEUSER AS a
     WHERE a.frequency='P'
     AND a.send_time=r.send_time)
     ENDIF
     ELSE
     IF current date+send_time
     >COALESCE(time_sent,current timestamp) THEN
     current date+send_time
     ELSE
     current date+send_time+1
     ENDIF
     ENDIF
     ENDIF) AS next_send,
         log_send,time_sent,log_sent,
         confirm_sent,send_count,resend_count,
         time_received,log_received,confirm_received,
         receive_count,rereceive_count
     FROM SYS.SYSREMOTEUSER AS r

Lists the information in SYSREMOTEUSER in a more readable format.

SYS.SYSTABAUTH

     CREATE VIEW SYS.SYSTABAUTH ( grantor, grantee,
         screator, stname, tcreator, ttname,
         selectauth, insertauth, deleteauth,
         updateauth, updatecols, alterauth, referenceauth )
     AS
     SELECT ( SELECT user_name FROM SYS.SYSUSERPERM
                 WHERE user_id = SYSTABLEPERM.grantor ),
             ( SELECT user_name FROM SYS.SYSUSERPERM
                 WHERE user_id = SYSTABLEPERM.grantee ),
             ( SELECT user_name
                 FROM SYS.SYSUSERPERM == SYS.SYSTABLE
                 WHERE table_id = SYSTABLEPERM.stable_id ),
             ( SELECT table_name FROM SYS.SYSTABLE
                 WHERE table_id = SYSTABLEPERM.stable_id ),
             ( SELECT user_name FROM
                 SYS.SYSUSERPERM == SYS.SYSTABLE
                 WHERE table_id = SYSTABLEPERM.ttable_id ),
             ( SELECT table_name FROM SYS.SYSTABLE
                 WHERE table_id = SYSTABLEPERM.ttable_id ),
             selectauth, insertauth, deleteauth,
             updateauth, updatecols,
             alterauth, referenceauthauth
     FROM SYS.SYSTABLEPERM

Presents table permission information in SYSTABLEPERM in a more readable format.

SYS.SYSTRIGGERS

     CREATE VIEW SYS.SYSTRIGGERS ( owner, trigname, tname,
     event, trigtime, trigdefn )
     AS
     SELECT ( SELECT user_name FROM SYS.SYSUSERPERM
                 WHERE user_id = SYSTABLE.creator ),
             trigger_name, table_name,
             IF event = 'I' THEN 'INSERT'
             ELSE IF event = 'U' THEN 'UPDATE'
             ELSE IF event = 'C' THEN 'UPDATE'
             ELSE 'DELETE' ENDIF ENDIF ENDIF,
             IF trigger_time = 'B' THEN 'BEFORE'
                 ELSE 'AFTER' ENDIF,
             trigger_defn
     FROM SYS.SYSTRIGGER == SYS.SYSTABLE
     WHERE foreign_table_id IS NULL

Lists all the triggers from SYSTRIGGER in a readable format.

SYS.SYSUSERAUTH

     CREATE VIEW SYS.SYSUSERAUTH ( name, password,     resourceauth, dbaauth, scheduleauth, user_group )
     AS
     SELECT user_name, password, resourceauth,
             dbaauth, scheduleauth, user_group
     FROM SYS.SYSUSERPERM

Displays all the information in the table SYSUSERPERM except for user numbers. Since this view shows passwords, this system view does not have PUBLIC select permission. (All other system views have PUBLIC select permission.)

SYS.SYSUSERLIST

     CREATE VIEW SYS.SYSUSERLIST ( name, resourceauth,
         dbaauth, scheduleauth, user_group )
     AS
     SELECT user_name, resourceauth,
             dbaauth, scheduleauth, user_group
     FROM SYS.SYSUSERPERM

Presents all information in SYSUSERAUTH except for passwords.

SYS.SYSUSEROPTIONS

     CREATE VIEW SYS.SYSUSEROPTIONS ( "user_name",
         "option", "setting" )
     AS
     SELECT u.name, "option",
             isnull( ( SELECT "setting"
                         FROM sys.sysoptions s
             WHERE s.user_name = u.name
             AND s."option" = o."option" ),
             "setting" )
     FROM SYS.SYSOPTIONS o, SYS.SYSUSERAUTH u
     WHERE o.user_name = 'PUBLIC'

Display effective permanent option settings for each user. If a user has no setting for an option, this view will display the public setting for the option.

SYS.SYSUSERPERMS

     CREATE VIEW SYS.SYSUSERPERMS
     AS
     SELECT user_id, user_name, resourceauth, dbaauth,
             scheduleauth, user_group, remarks
     FROM SYS.SYSUSERPERM

Contains exactly the same information as the table SYS.SYSUSERPERM except the password is omitted. All users have read access to this view, but only the DBA has access to the underlying table (SYS.SYSUSERPERM).

SYS.SYSVIEWS

     CREATE VIEW SYS.SYSVIEWS ( vcreator, viewname, viewtext )
     AS
     SELECT user_name, table_name, view_def
     FROM SYS.SYSTABLE KEY JOIN SYS.SYSUSERPERM
     WHERE table_type = 'VIEW'

Lists views along with their definitions.