Hello Bernd, the SYSJDBC schema is available since 7.06.00, however it is internal, and may change without notice. The other implementation in the JDBC driver is still used for version 7.5.00 and below.
For your purposes, it is more safe to refer to the DOMAIN.* tables and use SELECT DISTINCT TABLENAME, SCHEMANAME FROM DOMAIN.COLUMNS WHERE KEYPOS IS NOT NULL AND OWNER=USER and add the schema to the alter statement, as an user may have tables in another schema (from 7.6 on, where schemas are supported). Take care to enclose the table/schema name into " (i.e. "DBA"."MYTABLE"), as otherwise the name is uppered and probably the table isn't found. However - why one wants to drop a primary key on a table i cannot really imagine ... Regards Alexander Schröder SAP DB, SAP Labs Berlin > -----Original Message----- > From: Eckenfels. Bernd [mailto:[EMAIL PROTECTED] > Sent: Tuesday, June 28, 2005 6:47 AM > To: [email protected] > Subject: Information schema (Tables with PK) > > Hello, > > I have a Script which creates dbprocs, which are used to clean all > index, keys, foreign keys, comments, views, defaults and > contraints from > a given owner user. This script used to run against the DOMAIN.SHOW_ > system tables from 7.5. With 7.6 those are not present anymore, so I > converted all of them to the documented DOMAIN.* tables. (I > am not sure > why we didnt used them in the first place :) > > All but one: I am not sure how to replace this: > > CREATE DBPROC DROP_PRIMARY_KEY AS > VAR > ALTER_STATEMENT VARCHAR(60); > TABLE_NAME VARCHAR(32); > > DECLARE CUR_PKS CURSOR FOR > SELECT DISTINCT TABLENAME > FROM DOMAIN.SHOW_PRIMARY_KEY > where OWNER = user; > > WHILE $RC = 0 DO BEGIN > FETCH NEXT CUR_PKS INTO :TABLE_NAME; > > IF($rc = 100) THEN break; > > ALTER_STATEMENT = 'ALTER TABLE ' || TABLE_NAME || ' DROP PRIMARY > KEY'; > execute ALTER_STATEMENT; > > END; > CLOSE CUR_PKS; > GO > > > I checked the JDBC driver and the SapDB MetaData class is listening on > DOMAIN.COLUMS and the MAxDB Version is selecting on > SYSJDBC.PRIMARYKEYS. > Which one is the recommended way to get all tables with primary key > definitions? I think MaxDB as well as SAPDB do not support to name PKs > anyway? I think a specific system table should exist for > them, even if i > can guess them from the columns definition. > > Greetings > Bernd > > PS: if you wonder why we had to use the DBPROC it is due to syntax > limitations with stand-alone SQL statements via ANT-SQL Task. > > PPS: i read about the decisin to remove those oracle > information schema, > ist fine with me but I think there are DB analyser tools out > ther which > need to be modified now. > > PPPS: some optimizations for error handling, stability and identifier > quotings of the above code are welcome. Is there a MaxDB Wiki > somewhere? > -- > www.seeburger.com > Chief Architect (R&D) > > -- > MaxDB Discussion Mailing List > For list archives: http://lists.mysql.com/maxdb > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
