Added: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/syscat.out URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/syscat.out?rev=179674&view=auto ============================================================================== --- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/syscat.out (added) +++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/syscat.out Thu Jun 2 16:52:45 2005 @@ -0,0 +1,619 @@ +ij> -- +----- this test shows the system catalogs +----- +----- It is a goal of this test not to display information that +----- can (and will!) change from run to run, such as fields +----- that may eventually be UUIDs or UUID-like. +----- +maximumdisplaywidth 500; +ij> -- negative tests +----- verify no user ddl allowed on system tables +----- drop table +drop table sys.systables; +ERROR X0Y56: 'DROP TABLE' is not allowed on the System table 'SYS.SYSTABLES'. +ij> -- drop index +drop index sys.sysaliases_index2; +ERROR 42X62: 'DROP INDEX' is not allowed in the 'SYS' schema. +ij> -- create index +create index trash on sys.systables(tableid); +ERROR 42X62: 'CREATE INDEX' is not allowed in the 'SYS' schema. +ij> -- system tables are not updateable +autocommit off; +ij> delete from sys.systables; +ERROR 42Y25: 'SYS.SYSTABLES' is a system table. Users are not allowed to modify the contents of this table. +ij> update sys.systables set tablename = tablename | | 'trash'; +ERROR 42Y25: 'SYS.SYSTABLES' is a system table. Users are not allowed to modify the contents of this table. +ij> insert into sys.systables select * from sys.systables; +ERROR 42Y25: 'SYS.SYSTABLES' is a system table. Users are not allowed to modify the contents of this table. +ij> get cursor c as 'select tablename from sys.systables for update of tablename'; +ERROR 42Y90: FOR UPDATE is not permitted on this type of statement. +ij> -- users not allowed to do ddl in sys schema +create table sys.usertable(c1 int); +ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYS' schema. +ij> create view sys.userview as values 1; +ERROR 42908: The CREATE VIEW statement does not include a column list. +ij> rollback work; +ij> autocommit on; +ij> -- positive tests +create function gatp(SCH VARCHAR(128), TBL VARCHAR(128)) RETURNS VARCHAR(1000) +EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.TestPropertyInfo.getAllTableProperties' +LANGUAGE JAVA PARAMETER STYLE JAVA; +0 rows inserted/updated/deleted +ij> create function gaip(SCH VARCHAR(128), TBL VARCHAR(128)) RETURNS VARCHAR(1000) +EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.TestPropertyInfo.getAllIndexProperties' +LANGUAGE JAVA PARAMETER STYLE JAVA; +0 rows inserted/updated/deleted +ij> -- get the properties for the heaps +select tablename,gatp('SYS', + tablename) from sys.systables +order by tablename; +TABLENAME |2 +----- +SYSALIASES |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=12, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=false } +SYSCHECKS |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=12, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=false } +SYSCOLUMNS |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=12, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=false } +SYSCONGLOMERATES |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=12, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=false } +SYSCONSTRAINTS |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=12, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=false } +SYSDEPENDS |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=12, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=false } +SYSDUMMY1 |{ } +SYSFILES |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=12, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=false } +SYSFOREIGNKEYS |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=12, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=false } +SYSKEYS |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=12, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=false } +SYSSCHEMAS |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=12, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=false } +SYSSTATEMENTS |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=12, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=false } +SYSSTATISTICS |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=12, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=false } +SYSTABLES |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=12, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=false } +SYSTRIGGERS |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=12, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=false } +SYSVIEWS |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=12, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=false } +ij> -- get the properties for the indexes +select conglomeratename, gaip('SYS', + conglomeratename) from sys.sysconglomerates +where isindex +order by conglomeratename; +CONGLOMERATENAME |2 +----- +SYSALIASES_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } +SYSALIASES_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } +SYSALIASES_INDEX3 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } +SYSCHECKS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } +SYSCOLUMNS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } +SYSCOLUMNS_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } +SYSCONGLOMERATES_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } +SYSCONGLOMERATES_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } +SYSCONGLOMERATES_INDEX3 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } +SYSCONSTRAINTS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } +SYSCONSTRAINTS_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } +SYSCONSTRAINTS_INDEX3 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } +SYSDEPENDS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } +SYSDEPENDS_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } +SYSFILES_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } +SYSFILES_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } +SYSFOREIGNKEYS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } +SYSFOREIGNKEYS_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } +SYSKEYS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } +SYSSCHEMAS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } +SYSSCHEMAS_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } +SYSSTATEMENTS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } +SYSSTATEMENTS_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } +SYSSTATISTICS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } +SYSTABLES_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } +SYSTABLES_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } +SYSTRIGGERS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } +SYSTRIGGERS_INDEX2 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } +SYSTRIGGERS_INDEX3 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } +SYSVIEWS_INDEX1 |{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true } +ij> select TABLENAME, TABLETYPE from sys.systables; +TABLENAME |& +----- +SYSCONGLOMERATES |S +SYSTABLES |S +SYSCOLUMNS |S +SYSSCHEMAS |S +SYSCONSTRAINTS |S +SYSKEYS |S +SYSDEPENDS |S +SYSALIASES |S +SYSVIEWS |S +SYSCHECKS |S +SYSFOREIGNKEYS |S +SYSSTATEMENTS |S +SYSFILES |S +SYSTRIGGERS |S +SYSSTATISTICS |S +SYSDUMMY1 |S +ij> select TABLENAME, + COLUMNNAME, COLUMNNUMBER, + columndatatype +from sys.systables t, sys.syscolumns c +where t.TABLEID=c.REFERENCEID +order by TABLENAME, COLUMNNAME; +TABLENAME |COLUMNNAME |COLUMNNUMB& |COLUMNDATATYPE +----- +SYSALIASES |ALIAS |2 |VARCHAR(128) NOT NULL +SYSALIASES |ALIASID |1 |CHAR(36) NOT NULL +SYSALIASES |ALIASINFO |8 |org.apache.derby.catalog.AliasInfo +SYSALIASES |ALIASTYPE |5 |CHAR(1) NOT NULL +SYSALIASES |JAVACLASSNAME |4 |LONG VARCHAR NOT NULL +SYSALIASES |NAMESPACE |6 |CHAR(1) NOT NULL +SYSALIASES |SCHEMAID |3 |CHAR(36) +SYSALIASES |SPECIFICNAME |9 |VARCHAR(128) NOT NULL +SYSALIASES |SYSTEMALIAS |7 |BOOLEAN NOT NULL +SYSCHECKS |CHECKDEFINITION |2 |LONG VARCHAR NOT NULL +SYSCHECKS |CONSTRAINTID |1 |CHAR(36) NOT NULL +SYSCHECKS |REFERENCEDCOLUMNS |3 |org.apache.derby.catalog.ReferencedColumns NOT NULL +SYSCOLUMNS |AUTOINCREMENTINC |9 |BIGINT +SYSCOLUMNS |AUTOINCREMENTSTART |8 |BIGINT +SYSCOLUMNS |AUTOINCREMENTVALUE |7 |BIGINT +SYSCOLUMNS |COLUMNDATATYPE |4 |org.apache.derby.catalog.TypeDescriptor NOT NULL +SYSCOLUMNS |COLUMNDEFAULT |5 |java.io.Serializable +SYSCOLUMNS |COLUMNDEFAULTID |6 |CHAR(36) +SYSCOLUMNS |COLUMNNAME |2 |VARCHAR(128) NOT NULL +SYSCOLUMNS |COLUMNNUMBER |3 |INTEGER NOT NULL +SYSCOLUMNS |REFERENCEID |1 |CHAR(36) NOT NULL +SYSCONGLOMERATES |CONGLOMERATEID |8 |CHAR(36) NOT NULL +SYSCONGLOMERATES |CONGLOMERATENAME |4 |VARCHAR(128) +SYSCONGLOMERATES |CONGLOMERATENUMBER |3 |BIGINT NOT NULL +SYSCONGLOMERATES |DESCRIPTOR |6 |org.apache.derby.catalog.IndexDescriptor +SYSCONGLOMERATES |ISCONSTRAINT |7 |BOOLEAN +SYSCONGLOMERATES |ISINDEX |5 |BOOLEAN NOT NULL +SYSCONGLOMERATES |SCHEMAID |1 |CHAR(36) NOT NULL +SYSCONGLOMERATES |TABLEID |2 |CHAR(36) NOT NULL +SYSCONSTRAINTS |CONSTRAINTID |1 |CHAR(36) NOT NULL +SYSCONSTRAINTS |CONSTRAINTNAME |3 |VARCHAR(128) NOT NULL +SYSCONSTRAINTS |REFERENCECOUNT |7 |INTEGER NOT NULL +SYSCONSTRAINTS |SCHEMAID |5 |CHAR(36) NOT NULL +SYSCONSTRAINTS |STATE |6 |CHAR(1) NOT NULL +SYSCONSTRAINTS |TABLEID |2 |CHAR(36) NOT NULL +SYSCONSTRAINTS |TYPE |4 |CHAR(1) NOT NULL +SYSDEPENDS |DEPENDENTFINDER |2 |org.apache.derby.catalog.DependableFinder NOT NULL +SYSDEPENDS |DEPENDENTID |1 |CHAR(36) NOT NULL +SYSDEPENDS |PROVIDERFINDER |4 |org.apache.derby.catalog.DependableFinder NOT NULL +SYSDEPENDS |PROVIDERID |3 |CHAR(36) NOT NULL +SYSDUMMY1 |IBMREQD |1 |CHAR(1) +SYSFILES |FILEID |1 |CHAR(36) NOT NULL +SYSFILES |FILENAME |3 |VARCHAR(128) NOT NULL +SYSFILES |GENERATIONID |4 |BIGINT NOT NULL +SYSFILES |SCHEMAID |2 |CHAR(36) NOT NULL +SYSFOREIGNKEYS |CONGLOMERATEID |2 |CHAR(36) NOT NULL +SYSFOREIGNKEYS |CONSTRAINTID |1 |CHAR(36) NOT NULL +SYSFOREIGNKEYS |DELETERULE |4 |CHAR(1) NOT NULL +SYSFOREIGNKEYS |KEYCONSTRAINTID |3 |CHAR(36) NOT NULL +SYSFOREIGNKEYS |UPDATERULE |5 |CHAR(1) NOT NULL +SYSKEYS |CONGLOMERATEID |2 |CHAR(36) NOT NULL +SYSKEYS |CONSTRAINTID |1 |CHAR(36) NOT NULL +SYSSCHEMAS |AUTHORIZATIONID |3 |VARCHAR(128) NOT NULL +SYSSCHEMAS |SCHEMAID |1 |CHAR(36) NOT NULL +SYSSCHEMAS |SCHEMANAME |2 |VARCHAR(128) NOT NULL +SYSSTATEMENTS |COMPILATIONSCHEMAID |8 |CHAR(36) NOT NULL +SYSSTATEMENTS |LASTCOMPILED |7 |TIMESTAMP +SYSSTATEMENTS |SCHEMAID |3 |CHAR(36) NOT NULL +SYSSTATEMENTS |STMTID |1 |CHAR(36) NOT NULL +SYSSTATEMENTS |STMTNAME |2 |VARCHAR(128) NOT NULL +SYSSTATEMENTS |TEXT |6 |LONG VARCHAR NOT NULL +SYSSTATEMENTS |TYPE |4 |CHAR(1) NOT NULL +SYSSTATEMENTS |USINGTEXT |9 |LONG VARCHAR +SYSSTATEMENTS |VALID |5 |BOOLEAN NOT NULL +SYSSTATISTICS |COLCOUNT |7 |INTEGER NOT NULL +SYSSTATISTICS |CREATIONTIMESTAMP |4 |TIMESTAMP NOT NULL +SYSSTATISTICS |REFERENCEID |2 |CHAR(36) NOT NULL +SYSSTATISTICS |STATID |1 |CHAR(36) NOT NULL +SYSSTATISTICS |STATISTICS |8 |org.apache.derby.catalog.Statistics NOT NULL +SYSSTATISTICS |TABLEID |3 |CHAR(36) NOT NULL +SYSSTATISTICS |TYPE |5 |CHAR(1) NOT NULL +SYSSTATISTICS |VALID |6 |BOOLEAN NOT NULL +SYSTABLES |LOCKGRANULARITY |5 |CHAR(1) NOT NULL +SYSTABLES |SCHEMAID |4 |CHAR(36) NOT NULL +SYSTABLES |TABLEID |1 |CHAR(36) NOT NULL +SYSTABLES |TABLENAME |2 |VARCHAR(128) NOT NULL +SYSTABLES |TABLETYPE |3 |CHAR(1) NOT NULL +SYSTRIGGERS |ACTIONSTMTID |11 |CHAR(36) +SYSTRIGGERS |CREATIONTIMESTAMP |4 |TIMESTAMP NOT NULL +SYSTRIGGERS |EVENT |5 |CHAR(1) NOT NULL +SYSTRIGGERS |FIRINGTIME |6 |CHAR(1) NOT NULL +SYSTRIGGERS |NEWREFERENCINGNAME |17 |VARCHAR(128) +SYSTRIGGERS |OLDREFERENCINGNAME |16 |VARCHAR(128) +SYSTRIGGERS |REFERENCEDCOLUMNS |12 |org.apache.derby.catalog.ReferencedColumns +SYSTRIGGERS |REFERENCINGNEW |15 |BOOLEAN +SYSTRIGGERS |REFERENCINGOLD |14 |BOOLEAN +SYSTRIGGERS |SCHEMAID |3 |CHAR(36) NOT NULL +SYSTRIGGERS |STATE |8 |CHAR(1) NOT NULL +SYSTRIGGERS |TABLEID |9 |CHAR(36) NOT NULL +SYSTRIGGERS |TRIGGERDEFINITION |13 |LONG VARCHAR +SYSTRIGGERS |TRIGGERID |1 |CHAR(36) NOT NULL +SYSTRIGGERS |TRIGGERNAME |2 |VARCHAR(128) NOT NULL +SYSTRIGGERS |TYPE |7 |CHAR(1) NOT NULL +SYSTRIGGERS |WHENSTMTID |10 |CHAR(36) +SYSVIEWS |CHECKOPTION |3 |CHAR(1) NOT NULL +SYSVIEWS |COMPILATIONSCHEMAID |4 |CHAR(36) NOT NULL +SYSVIEWS |TABLEID |1 |CHAR(36) NOT NULL +SYSVIEWS |VIEWDEFINITION |2 |LONG VARCHAR NOT NULL +ij> select TABLENAME, ISINDEX +from sys.systables t, sys.sysconglomerates c +where t.TABLEID=c.TABLEID +order by TABLENAME, ISINDEX; +TABLENAME |ISIND& +----- +SYSALIASES |0 +SYSALIASES |1 +SYSALIASES |1 +SYSALIASES |1 +SYSCHECKS |0 +SYSCHECKS |1 +SYSCOLUMNS |0 +SYSCOLUMNS |1 +SYSCOLUMNS |1 +SYSCONGLOMERATES |0 +SYSCONGLOMERATES |1 +SYSCONGLOMERATES |1 +SYSCONGLOMERATES |1 +SYSCONSTRAINTS |0 +SYSCONSTRAINTS |1 +SYSCONSTRAINTS |1 +SYSCONSTRAINTS |1 +SYSDEPENDS |0 +SYSDEPENDS |1 +SYSDEPENDS |1 +SYSDUMMY1 |0 +SYSFILES |0 +SYSFILES |1 +SYSFILES |1 +SYSFOREIGNKEYS |0 +SYSFOREIGNKEYS |1 +SYSFOREIGNKEYS |1 +SYSKEYS |0 +SYSKEYS |1 +SYSSCHEMAS |0 +SYSSCHEMAS |1 +SYSSCHEMAS |1 +SYSSTATEMENTS |0 +SYSSTATEMENTS |1 +SYSSTATEMENTS |1 +SYSSTATISTICS |0 +SYSSTATISTICS |1 +SYSTABLES |0 +SYSTABLES |1 +SYSTABLES |1 +SYSTRIGGERS |0 +SYSTRIGGERS |1 +SYSTRIGGERS |1 +SYSTRIGGERS |1 +SYSVIEWS |0 +SYSVIEWS |1 +ij> create table t (i int, s smallint); +0 rows inserted/updated/deleted +ij> select TABLENAME, TABLETYPE from sys.systables; +TABLENAME |& +----- +SYSCONGLOMERATES |S +SYSTABLES |S +SYSCOLUMNS |S +SYSSCHEMAS |S +SYSCONSTRAINTS |S +SYSKEYS |S +SYSDEPENDS |S +SYSALIASES |S +SYSVIEWS |S +SYSCHECKS |S +SYSFOREIGNKEYS |S +SYSSTATEMENTS |S +SYSFILES |S +SYSTRIGGERS |S +SYSSTATISTICS |S +SYSDUMMY1 |S +T |T +ij> select TABLENAME, + COLUMNNAME, COLUMNNUMBER, + columndatatype +from sys.systables t, sys.syscolumns c +where t.TABLEID=c.REFERENCEID +order by TABLENAME, COLUMNNAME; +TABLENAME |COLUMNNAME |COLUMNNUMB& |COLUMNDATATYPE +----- +SYSALIASES |ALIAS |2 |VARCHAR(128) NOT NULL +SYSALIASES |ALIASID |1 |CHAR(36) NOT NULL +SYSALIASES |ALIASINFO |8 |org.apache.derby.catalog.AliasInfo +SYSALIASES |ALIASTYPE |5 |CHAR(1) NOT NULL +SYSALIASES |JAVACLASSNAME |4 |LONG VARCHAR NOT NULL +SYSALIASES |NAMESPACE |6 |CHAR(1) NOT NULL +SYSALIASES |SCHEMAID |3 |CHAR(36) +SYSALIASES |SPECIFICNAME |9 |VARCHAR(128) NOT NULL +SYSALIASES |SYSTEMALIAS |7 |BOOLEAN NOT NULL +SYSCHECKS |CHECKDEFINITION |2 |LONG VARCHAR NOT NULL +SYSCHECKS |CONSTRAINTID |1 |CHAR(36) NOT NULL +SYSCHECKS |REFERENCEDCOLUMNS |3 |org.apache.derby.catalog.ReferencedColumns NOT NULL [... 297 lines stripped ...]