Home Page for the TeradataForum
 

 

Library: White Papers


 

Reference Tables for AccessRights and TableKind

Written by Ulrich Arndt    (Data2Knowledge)


These two tables have been provided to help translate AccessRights and Tablekind fields.


ACCESSRIGHT

To view or download the source: accessright_ref.sql.


     /*********************************************************************************
     **
     ** Source:      NCR Documentation
     **              Data Dictionary
     **              Release V2R6.1
     **              B035-1092-115A
     **              November 2005
     **
     ** Author of statements: Ulrich Arndt - www.data2knowledge.de
     **                       ulrich.arndt@data2knowledge.de
     **
     ** First written:       2006/02/15
     **                      2006/03/16 Add comments/corrections of Mike Dempsey
     **
     ** NO WARRANTY ON CORRECTNESS AND COMPLETENESS OF  THIS SCRIPT AND IT CONTENT!
     **
     ***********************************************************************************/

     CREATE TABLE ACCESSRIGHT_V2R61_REF
     (
      ACCESSRIGHT CHAR(2) NOT NULL,
      ACCESSRIGHT_DESC VARCHAR(10000) NOT NULL
     ) UNIQUE PRIMARY INDEX (ACCESSRIGHT);

     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('AE','ALTER EXTERNAL PROCEDURE');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('AF','ALTER FUNCTION');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('AP','ALTER PROCEDURE');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('AS','ABORT SESSION');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('CA','CREATE AUTHORIZATION');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('CD','CREATE DATABASE');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('CE','CREATE EXTERNAL PROCEDURE');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('CF','CREATE FUNCTION');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('CG','CREATE TRIGGER');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('CM','CREATE MACRO');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('CO','CREATE PROFILE');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('CP','CHECKPOINT');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('CR','CREATE ROLE');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('CT','CREATE TABLE');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('CU','CREATE USER');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('CV','CREATE VIEW');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('D','DELETE');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('DA','DROP AUTHORIZATION');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('DD','DROP DATABASE');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('DF','DROP FUNCTION');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('DG','DROP TRIGGER');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('DM','DROP MACRO');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('DO','DROP PROFILE');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('DP','DUMP');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('DR','DROP ROLE');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('DT','DROP TABLE');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('DU','DROP USER');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('DV','DROP VIEW');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('E','EXECUTE (MACRO)');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('EF','EXECUTE FUNCTION');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('I','INSERT');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('IX','INDEX');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('MR','MONITOR RESOURCE');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('MS','MONITOR SESSION');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('PC','CREATE PROCEDURE');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('PD','DROP PROCEDURE');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('PE','EXECUTE PROCEDURE');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('R','RETRIEVE/SELECT');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('RF','REFERENCE');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('RS','RESTORE');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('RO','REPLICATION CONTROL or REPLICATION OVERIDE');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('SR','SET RESOURCE RATE');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('SS','SET SESSION RATE');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('U','UPDATE');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('UM','UDT METHOD');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('UT','UDT TYPE');
     INSERT INTO ACCESSRIGHT_V2R61_REF VALUES ('UU','UDT USAGE');

     COLLECT STATS ACCESSRIGHT_V2R61_REF INDEX (ACCESSRIGHT);

     CREATE VIEW V_ACCESSRIGHT_REF
     AS
     SELECT ACCESSRIGHT,
            ACCESSRIGHT_DESC
     FROM ACCESSRIGHT_V2R61_REF;

     /* CHECK IF CURRENTLY COMPLET */
     SELECT T.ACCESSRIGHT
     FROM DBC.ALLRIGHTS T
     WHERE NOT EXISTS
           (SELECT *
            FROM V_ACCESSRIGHT_REF R
            WHERE R.ACCESSRIGHT = T.ACCESSRIGHT
           )
     GROUP BY ACCESSRIGHT
     ORDER BY ACCESSRIGHT
     ;

TABLEKIND

To view or download the source: tablekind_ref.sql.


     /*********************************************************************************
     **
     ** Source:      NCR Documentation
     **              Data Dictionary
     **              Release V2R6.1
     **              B035-1092-115A
     **              November 2005
     **
     ** Author of statements: Ulrich Arndt - www.data2knowledge.de
     **                       ulrich.arndt@data2knowledge.de
     **
     ** First written:       2006/02/15
     **                      2006/03/16 Add comments/corrections of Mike Dempsey
     **
     ** NO WARRANTY ON CORRECTNESS AND COMPLETENESS OF  THIS SCRIPT AND IT CONTENT!
     **
     ***********************************************************************************/

     CREATE TABLE TABLEKIND_V2R61_REF
     (
      TABLEKIND CHAR(2) NOT NULL,
      TABLEKIND_DESC VARCHAR(10000) NOT NULL
     ) UNIQUE PRIMARY INDEX (TABLEKIND);

     INSERT INTO TABLEKIND_V2R61_REF VALUES ('A','AGGREGATE UDF');
     INSERT INTO TABLEKIND_V2R61_REF VALUES ('B','COMBINED AGGREGATE AND ORDERED ANALYTICAL FUNCTION');
     INSERT INTO TABLEKIND_V2R61_REF VALUES ('E','EXTERNAL STORED PROCEDURE');
     INSERT INTO TABLEKIND_V2R61_REF VALUES ('F','SCALAR UDF');
     INSERT INTO TABLEKIND_V2R61_REF VALUES ('G','TRIGGER');
     INSERT INTO TABLEKIND_V2R61_REF VALUES ('H','INSTANCE OR CONSTRUCTOR METHOD');
     INSERT INTO TABLEKIND_V2R61_REF VALUES ('I','JOIN INDEX');
     INSERT INTO TABLEKIND_V2R61_REF VALUES ('J','JOURNAL');
     INSERT INTO TABLEKIND_V2R61_REF VALUES ('M','MACRO');
     INSERT INTO TABLEKIND_V2R61_REF VALUES ('N','HASH INDEX');
     INSERT INTO TABLEKIND_V2R61_REF VALUES ('P','STORED PROCEDURE');
     INSERT INTO TABLEKIND_V2R61_REF VALUES ('Q','QUEUE TABLE');
     INSERT INTO TABLEKIND_V2R61_REF VALUES ('R','TABLE FUNCTION');
     INSERT INTO TABLEKIND_V2R61_REF VALUES ('S','ORDERED ANALYTICAL FUNCTION');
     INSERT INTO TABLEKIND_V2R61_REF VALUES ('T','TABLE');
     INSERT INTO TABLEKIND_V2R61_REF VALUES ('U','USER-DEFINED DATA TYPE');
     INSERT INTO TABLEKIND_V2R61_REF VALUES ('V','VIEW');
     INSERT INTO TABLEKIND_V2R61_REF VALUES ('X','AUTHORIZATION');

     COLLECT STATS TABLEKIND_V2R61_REF INDEX (TABLEKIND);

     CREATE VIEW V_TABLEKIND_REF
     AS
     SELECT TABLEKIND,
            TABLEKIND_DESC
     FROM TABLEKIND_V2R61_REF;


     /* CHECK IF CURRENTLY COMPLET */
     SELECT T.TABLEKIND
     FROM DBC.TABLES T
     WHERE NOT EXISTS
           (SELECT *
            FROM V_TABLEKIND_REF R
            WHERE R.TABLEKIND = T.TABLEKIND
           )
     GROUP BY TABLEKIND
     ORDER BY TABLEKIND
     ;





 
  Top Home Privacy Feedback  
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky
Copyright 2016 - All Rights Reserved
Last Modified: 28 Dec 2016