System Stored Procedures
System stored procedures help you accomplish those administrative and informative tasks not covered by the Data Definition Language.
Zen System Stored Procedures
System stored procedures have a psp_ prefix. The following table lists the system stored procedures currently supported in Zen.
Unless otherwise noted, examples for system stored procedures use the Demodata sample database or refer to Zen system tables.
If you execute a system stored procedure within a database to obtain information from a secured database, an error occurs. You cannot access information in a secured database from any other database.
Note: Do not create stored procedures with the psp_ prefix in their name. Any user-created stored procedure with the same name as a system stored procedure will fail to execute.
psp_columns
Returns the list of columns and their corresponding information for a specified table, from the current database or the specified database.
Syntax
call psp_columns(['database_qualifier'],'table_name', ['column_name'])
Arguments
Parameter | Type | Default Value | Description |
|---|---|---|---|
Database_qualifier | VARCHAR(20) | Current database | Name of database from which to obtain details |
table_name | VARCHAR(255) | (no default value) | Name of table whose column information is required |
column_name | VARCHAR(255) | All columns for the table | Column name of the table specified |
Returned Result Set
Column Name | Data Type | Description |
|---|---|---|
TABLE_QUALIFIER | VARCHAR (20) | Name of the database |
TABLE_OWNER | VARCHAR (20) | Name of the table owner. Table owner is reserved for future use. It currently returns empty (NULL). |
TABLE_NAME | VARCHAR(255) | Name of the table |
COLUMN_NAME | VARCHAR(255) | Column name of the table |
DATA_TYPE | SMALLINT | Data type code of the column. See Zen Supported Data Types. |
TYPE_NAME | VARCHAR (32) | Name of the data type of the column corresponding to DATA_TYPE value |
PRECISION | INTEGER | The precision of the column if the data type is Decimal, Numeric, and so forth. See Precision and Scale of Decimal Data Types. |
LENGTH | INTEGER | The length of the column. |
SCALE | SMALLINT | The scale of the column if the data type is Decimal, Numeric, and so forth. |
RADIX | SMALLINT | Base for numeric data types |
NULLABLE | SMALLINT | Specifies nullability: 1 - NULL allowed 0 - NULL not allowed |
REMARKS | VARCHAR(255) | Remarks field |
Example
create table tx (c_binary BINARY(10),
c_char CHAR(10),
c_tinyint TINYINT,
c_smallint SMALLINT,
c_int INT,
c_bigint BIGINT,
c_utinyint UTINYINT);
call psp_columns(, 'tx',);
Result Set
Table_ qualifier | Table_owner | Table_name | Column_name | Data_ type | Type_name | P | L | S | R | N | R |
|---|---|---|---|---|---|---|---|---|---|---|---|
'demodata' | Null | tx | C_binary | -2 | Binary | 10 | 10 | Null | Null | 1 | Null |
'demodata' | Null | tx | C_char | -1 | Char | 10 | 10 | Null | Null | 1 | Null |
'demodata' | Null | tx | C_tinyint | -6 | Tinyint | Null | 1 | 0 | 10 | 1 | Null |
..... | |||||||||||
Legend: P = Precision; L = Length; S = Scale; R = Radix; N = Nullable; R = Remarks | |||||||||||
============
Assume that you have a database named mydatabase that contains a table named tx.
call psp_columns('mydatabase', 'tx', )
Result Set
Table_ qualifier | Table_owner | Table_name | Column_name | Data_type | Type_ name | P | L | S | R | N | R |
|---|---|---|---|---|---|---|---|---|---|---|---|
'wsrde' | Null | tx | C_binary | -2 | Binary | 10 | 10 | Null | Null | 1 | Null |
'wsrde' | Null | tx | C_char | -1 | Char | 10 | 10 | Null | Null | 1 | Null |
'wsrde' | Null | tx | C_tinyint | -6 | Tinyint | Null | 1 | 0 | 10 | 1 | Null |
..... | |||||||||||
Legend: P = Precision; L = Length; S = Scale; R = Radix; N = Nullable; R = Remarks | |||||||||||
============
call psp_columns('mydatabase', 'tx', 'c_binary')
Result Set
Table_ qualifier | Table_ owner | Table_ name | Column_ name | Data_ type | Type_ name | P | L | S | R | N | R |
|---|---|---|---|---|---|---|---|---|---|---|---|
'wsrde' | Null | tx | C_binary | -2 | Binary | 10 | 10 | Null | Null | 1 | Null |
Legend: P = Precision; L = Length; S = Scale; R = Radix; N = Nullable; R = Remarks | |||||||||||
Error Conditions
Condition | Error Message |
|---|---|
database_qualifier is a blank string | Please enter a valid database name. Database name cannot be a blank string. |
database_qualifier is an undefined database | Unable to open table: X$File |
table_name is undefined in the database | No error is returned and no results are returned |
table_name is null | Table name cannot be null |
table_name is a blank string | Table name cannot be a blank string |
column_name is a blank string | Column name cannot be a blank string |
column_name is undefined in the table | No error is returned and no results are returned |
psp_column_attributes
Returns the list of column attributes and the corresponding information from the current database or the specified database.
Syntax
call psp_column_attributes(['database_qualifier'], ['table_name'], ['column_name'])
Arguments
Parameter | Type | Default Value | Description |
|---|---|---|---|
Database_qualifier | VARCHAR(20) | Current database you are logged in | Name of the database from which the details are to be obtained |
table_name | VARCHAR(255) | All tables for the specified database | Name of the table whose column information is required |
column_name | VARCHAR(255) | All columns for the specified table | Column name of the table specified |
Returned Result Set
Column Name | Data Type | Description |
|---|---|---|
TABLE_QUALIFIER | VARCHAR (20) | Name of the database |
TABLE_OWNER | VARCHAR (20) | Name of the table owner |
TABLE_NAME | VARCHAR(255) | Name of the table |
COLUMN_NAME | VARCHAR(255) | Column name of the table |
ATTRIB_TYPE | CHAR(10) | "Default" if a default value assigned to the column "Collate" if the column uses a collating sequence "L" if the column has a logical positioning Null or blank for all other types of attributes |
ATTRIB_SIZE | USMALLINT | Size of the column attribute |
ATTRIB_VALUE | LONGVARCHAR | Value of the column attribute |
Examples
create table tx (c_binary binary (10) default 01,
c_char char (11) default 'thisisatest',
c_tinyint TINYINT,
c_SMALLINT SMALLINT,
c_int INT,
c_bigint BIGINT,
c_utinyint uTINYINT);
call psp_column_attributes(, , );
Result Set
Table_ qualifier | Table_ owner | Table_ name | Column_ name | Attrib_ Type | Attrib_ Size | Attrib_ Value |
|---|---|---|---|---|---|---|
'demodata' | Null | tx | C_binary | Default | 2 | 01 |
'demodata' | Null | tx | C_char | Default | 11 | 'Thisisatest' |
============
create table tlogicalmv (col1 integer, col2 char(20))
alter table tlogicalmv psql_move col1 to 2
call psp_column_attributes(, 'tlogicalmv' , )
Result Set
Table_ qualifier | Table_ owner | Table_ name | Column_ name | Attrib_ Type | Attrib_ Size | Attrib_ Value |
|---|---|---|---|---|---|---|
'demodata' | Null | tlogicalmv | col2 | L | 1 | 1 |
'demodata' | Null | tlogicalmv | col1 | L | 1 | 2 |
============
call psp_column_attributes(, 'tx', 'c_binary')
Result Set
Table_ qualifier | Table_ owner | Table_ name | Column_ name | Attrib_ Type | Attrib_ Size | Attrib_ Value |
|---|---|---|---|---|---|---|
'demodata' | Null | tx | C_binary | Default | 2 | 01 |
Error Conditions
Condition | Error Message |
|---|---|
database_qualifier is a blank string | Please enter a valid database name. Database name cannot be a blank string. |
database_qualifier is an undefined database | Unable to open table: X$File |
table_name is undefined in the database | No error is returned and no results are returned |
table_name is null | Table name cannot be null |
table_name is a blank string | Table name cannot be a blank string |
column_name is a blank string | Column name cannot be a blank string |
column_name is undefined in the table | No error is returned and no results are returned |
psp_column_rights
Returns the list of column rights and corresponding information for the specified table, from the current database or the specified database.
Note: This system stored procedure returns the list of column rights only if it has been explicitly specified using the GRANT syntax.
Syntax
call psp_column_rights(['database_qualifier'], 'table_name', ['column_name'], ['user_name'])
Arguments
Parameter | Type | Default Value | Description |
|---|---|---|---|
Database_qualifier | VARCHAR(20) | Current database | Name of the database from which the details are to be obtained |
table_name | VARCHAR(255) | (no default value) | Name of the table for which rights have been specified |
column_name | VARCHAR(255) | All columns of the specified table | Name of the column whose rights are to be obtained |
user_name | VARCHAR(255) | Current user | Name of the user for whom the list of column rights need to be obtained. Pattern matching is supported. |
Returned Result Set
Column Name | Data Type | Description |
|---|---|---|
TABLE_QUALIFIER | VARCHAR (20) | Name of the database |
TABLE_OWNER | VARCHAR (20) | Name of the owner of the table |
USER_NAME (GRANTEE) | VARCHAR(255) | Name of the user |
TABLE_NAME | VARCHAR(255) | Name of the table |
COLUMN_NAME | VARCHAR(255) | Name of the column for which the different rights have been granted |
RIGHTS | VARCHAR(12) | One of the following values: SELECT UPDATE INSERT |
Example
After granting the following permissions on table Dept in the Demodata database, retrieve the column permissions:
GRANT SELECT(Name, Building_Name) ON Dept TO John;
GRANT UPDATE(Name) ON Dept TO Mary;
GRANT INSERT(Building_Name) ON Dept TO John;
Call psp_column_rights(,'Dept', ,'%');
Result Set
Table_Qualifier | Table_owner | User_name | Table_name | Column_name | Rights |
|---|---|---|---|---|---|
Demodata | Null | John | Dept | Name | SELECT |
Demodata | Null | John | Dept | Building_name | SELECT |
Demodata | Null | John | Dept | Building_name | INSERT |
Demodata | Null | Mary | Dept | Name | UPDATE |
Note: User Master has no explicit column rights defined, so psp_column_rights returns no results for that user.
Assume that user John is logged on to the database. The following statement prints column permissions on table Dept table for user John.
call psp_column_rights ('demodata', 'Dept', ,)
Result Set
Table_Qualifier | Table_owner | User_name | Table_name | Column_name | Rights |
|---|---|---|---|---|---|
Demodata | Null | John | Dept | Building_name | INSERT |
Demodata | Null | John | Dept | Building_name | SELECT |
Demodata | Null | John | Dept | Name | SELECT |
Note: If a user has been granted rights at the table level (for example, GRANT SELECT ON Dept TO Mary), a call to psp_column_rights returns no rights. The rights were granted to the table, not to specific columns.
The following statement prints column permissions on table Dept for column Name for the current user.
call psp_column_rights ('demodata', 'dept', 'name',)
Result Set
Table_Qualifier | Table_owner | User_name | Table_name | Column_name | Rights |
|---|---|---|---|---|---|
Demodata | Null | John | Dept | Name | SELECT |
The following statement prints column permissions on table Dept for user Mary:
call psp_column_rights('demodata', 'dept', , 'Mary')
Result Set
Table_Qualifier | Table_owner | User_name | Table_name | Column_name | Rights |
|---|---|---|---|---|---|
Demodata | Null | Mary | Dept | Name | UPDATE |
Error Conditions
Condition | Error Message |
|---|---|
database_qualifier is a blank string | Please enter a valid database name. Database name cannot be a blank string. |
table_name is null | Table name cannot be null. |
table_name is a blank string | Table name cannot be a blank string. |
column_name is a blank string | Column name cannot be a blank string. |
user_name is a blank string | User name cannot be a blank string. |
psp_dbnames
Returns the list of DB names on the server.
Syntax
call psp_dbnames(['dbnames.cfg_location' | null], ['dbname' | partial_dbname with wild card | null])
Arguments
Parameter | Type | Default Value | Description |
|---|---|---|---|
dbnames.cfg_location | VARCHAR(1000) | (no default value) | Location of dbnames.cfg on the server or null to drop x$dbnames in dictionary. |
dbname | VARCHAR(21) | (no default value) | Exact dbname or partial dbname with wild card for LIKE. Can accept null. |
Note: If dbnames.cfg_location is null, then the returned result set is null.
Returned Result Set
Name | Data Type | Description |
|---|---|---|
dbname | VARCHAR (21) | Name of the database |
Example
Assume that the file dbnames.cfg is located at C:\ProgramData\Actian\Zen. To demonstrate psp_dbnames behavior, execute the following:
1. This statement returns a list of database names on the server:
call psp_dbnames ( 'C:\ProgramData\Actian\Zen', null );
2. If an incorrect location is provided for dbnames.cfg, then an error occurs. For example:
call psp_dbnames ( 'C:\ProgramData\Actian\Zen2', null );
The database engine returns the error message "The Btrieve file directory is invalid (Btrieve Error 35)."
3. To recover from the error, run the following statement to reset psp_dbnames.
call psp_dbnames ( null, null );
The statement returns null.
psp_fkeys
Returns the foreign key information for the specified table in the current database.
Syntax
call psp_fkeys(['table_qualifier'], 'pkey_table_name', ['fkey_table_name'])
Arguments
Parameter | Type | Default Value | Description |
|---|---|---|---|
table_qualifier | VARCHAR(20) | Current database | Name of the database from which the details are to be obtained |
pkey_table_name | VARCHAR(255) | (no default value) | Name of the table whose foreign key is associated with the primary key column |
fkey_table_name | VARCHAR(255) | (no default value) | Name of the table whose foreign key information needs to be obtained |
Returned Result Set
Column Name | Data Type | Description |
|---|---|---|
PKTABLE_QUALIFIER | VARCHAR (20) | Database name of the primary key table |
PKTABLE_OWNER | VARCHAR (20) | Name of the owner of the primary key table |
PKTABLE_NAME | VARCHAR(255) | Name of the primary key table |
PKCOLUMN_NAME | VARCHAR(255) | Column name of the primary key column. |
KEY_SEQ | USMALLINT | Key sequence. This column value corresponds to Xi$Part in X$Index. See X$Index. |
FKTABLE_QUALIFIER | VARCHAR (20) | Database name of the foreign key table |
FKTABLE_OWNER | VARCHAR (20) | Name of the owner of the foreign key table |
FKTABLE_NAME | VARCHAR(255) | Name of the foreign key table |
FKCOLUMN_NAME | VARCHAR(255) | Column name of the foreign key column. |
UPDATE_RULE | Utinyint | Update Rule |
DELETE_RULE | Utinyint | Delete Rule |
PK_NAME | VARCHAR(255) | Name of the primary key |
FK_NAME | VARCHAR(255) | Name of the foreign key |
Example
CREATE TABLE Employee
(
Id INTEGER NOT NULL,
Name VARCHAR(50) NOT NULL,
SupId INTEGER NOT NULL
);
ALTER TABLE Employee
ADD CONSTRAINT EmpPkey
PRIMARY KEY(Id);
ALTER TABLE Employee
ADD CONSTRAINT ForgnKey
FOREIGN KEY(SupId) REFERENCES
Employee(Id) ON DELETE CASCADE;
call psp_fkeys(,'Employee',);
Result Set
PkQ | PkO | PkT | PkCol | Seq | FkQ | FkO | FkT | FkCol | UR | DR | PK | FK |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
Demodata | Null | Em- ployee | Id | 0 | Demodata | Null | Em- ployee | Supid | 1 | 2 | EmpPkey | ForgnKey |
Legend: PkQ = Pkey_table_qualifier; PkO = Pkey_table_owner; PkT = Pktable_name; PkCol = Pk_column_name; Seq = Key_seq; FkQ = Fktable_qualifier; FkO = Fktable_owner; FkT = Fktable_name; FkCol = Fkcolumn_name; UR = Update_rule; DR = Delete_rule; Pk = Pk_name; FK = Fk_name | ||||||||||||
Error Conditions
Condition | Error Message |
|---|---|
table_qualifier is a blank string | Table name cannot be a blank string. |
pKey_table_name is a blank string | Primary key table name cannot be a blank string. |
pKey_table_name is null | No argument or default value supplied. Argument: 2. |
fKey_table_name is a blank string | Foreign key table name cannot be a blank string. |
psp_groups
Returns the list of groups and the corresponding information from the current database or the specified database.
Syntax
call psp_groups(['database_qualifier'], ['group_name'])
Arguments
Parameter | Type | Default Value | Description |
|---|---|---|---|
Database_qualifier | VARCHAR(20) | Current database | Name of the database from which the details are to be obtained |
group_name | VARCHAR(255) | (no default value) | Name of the group used to return group information. Pattern matching is supported. |
Returned Result Set
Column Name | Data Type | Description |
|---|---|---|
DATABASE_QUALIFIER | VARCHAR (20) | Name of the database |
GROUP_ID | USMALLINT | Group Id |
GROUP_NAME | VARCHAR (255) | Name of the group |
Example
Assume that the Demodata sample database has two groups defined: DevGrp1 and DevGrp2.
call psp_groups(,)
Result Set
Database_qualifier | Group_Id | Group_Name |
|---|---|---|
Demodata | 1 | PUBLIC |
Demodata | 2 | DevGrp1 |
Demodata | 3 | DevGrp2 |
============
call psp_groups('Demodata', 'D%')
Result Set
Database_qualifier | Group_Id | Group_Name |
|---|---|---|
Demodata | 2 | DevGrp1 |
Demodata | 3 | DevGrp2 |
Error Conditions
Condition | Error Message |
|---|---|
database_qualifier is a blank string | Please enter a valid database name. Database name cannot be a blank string. |
group_name is a blank string | Group name cannot be a blank string. |
psp_help_sp
Returns the definition text of a given stored procedure from the current database or the specified database.
Syntax
call psp_help_sp('[database_qualifier'], 'procedure_name')
Arguments
Parameter | Type | Default Value | Description |
|---|---|---|---|
Database_qualifier | VARCHAR(20) | Current database | Name of the database from which the details are to be obtained |
procedure_name | CHAR(255) | (no default value) | Name of the procedure whose definition text is required. Pattern matching is not supported. |
Returned Result Set
Column Name | Data Type | Description |
|---|---|---|
DATABASE_QUALIFIER | VARCHAR (20) | Name of the database |
SP_TEXT | LONGVARCHAR | Stored procedure definition text |
Example
Assume that the Demodata sample database contains the following stored procedure saved as "Myproc."
Create procedure Myproc(:a integer, OUT :b integer) as
Begin
Set :a = :a + 10;
Set :b = :a;
End
The following statement prints the definition text for stored procedure "Myproc" in the current database.
call psp_help_sp(, 'Myproc')
Result Set
Database_Qualifier | SP_TEXT |
|---|---|
Demodata | Create procedure Myproc(:a integer, OUT :b integer) as Begin Set :a = :a + 10; Set :b = :a; End |
============
Assume that a database named "wsrde" contains the following stored procedure saved as "Myproc1."
Create procedure Myproc1(:a integer) returns (name char(20))
as
Begin
Select name from employee where Id =:a;
End
The following statement prints the definition text for stored procedure "Myproc1" in database "wsrde."
call psp_help_sp('wsrde', 'Myproc1')
Result Set
Database_Qualifier | SP_TEXT |
|---|---|
wsrde | Create procedure Myproc1(:a integer) returns (name char(20)) as Begin Select name from employee where Id =:a; End |
Error Conditions
Condition | Error Message |
|---|---|
database_qualifier is a blank string or null | Please enter a valid database name. Database name cannot be a blank string |
procedure_name is null | No argument or default value supplied. |
procedure_name is a blank string | Procedure name cannot be a blank string. |
psp_help_trigger
Returns the definition text of a trigger from the current database or the specified database.
Syntax
call psp_help_trigger (['database_qualifier'], 'trigger_name')
Arguments
Parameter | Type | Default Value | Description |
|---|---|---|---|
Database_qualifier | VARCHAR(20) | Current database | Name of the database from which the details are to be obtained |
trigger_name | VARCHAR(255) | (no default value) | Name of the trigger whose definition text is to be returned. Pattern matching is supported. |
Returned Result Set
Column Name | Data Type | Description |
|---|---|---|
DATABASE_QUALIFIER | VARCHAR (20) | Name of the database |
TRIGGER_TEXT | LONGVARCHAR | Trigger definition text. |
Example
The following statement prints the definition of the 'MyInsert' trigger:
CREATE TABLE A
(
col1 INTEGER,
col2 CHAR(255)
);
CREATE TABLE B
(
col1 INTEGER,
col2 CHAR(255)
);
CREATE TRIGGER MyInsert
AFTER INSERT ON A
FOR EACH ROW
INSERT INTO B VALUES
(NEW.col1, NEW.col2);
call psp_help_trigger(,'MyIns%');
Result Set
Database_Qualifier | TRIGGER_TEXT |
|---|---|
Demodata | CREATE TRIGGER MyInsert AFTER INSERT ON A FOR EACH ROW INSERT INTO B VALUES (NEW.col1, NEW.col2); |
Error Conditions
Condition | Error Message |
|---|---|
database_qualifier is a blank string | Please enter a valid database name. Database name cannot be a blank string |
trigger_name is null | No argument or default value supplied. |
trigger_name is a blank string | Trigger name cannot be a blank string. |
psp_help_udf
Returns the text of a given user-defined function (UDF) from the current database or the specified database.
Syntax
call psp_help_udf (['database_qualifier'], 'udf_name')
Arguments
Parameter | Type | Default Value | Description |
|---|---|---|---|
Database_qual | VARCHAR(20) | Current database | Name of the database from which the details are to be obtained |
udf_name | VARCHAR(255) | (no default value) | Name of the user-defined function whose function text is required. Pattern matching is supported. |
Returned Result Set
Column Name | Data Type | Description |
|---|---|---|
DATABASE_QUALIFIER | VARCHAR (20) | Name of the database |
UDF_TEXT | LONGVARCHAR | The text of the User Defined Function |
Example
call psp_help_udf(, 'Myfunction')
Result Set
Database_Qualifier | UDF_TEXT |
|---|---|
Demodata | Create function Myfunction(:a integer) Returns integer as Begin Return :a * :a; End |
============
call psp_help_udf('mydbase', 'Get%')
Result Set
Database_Qualifier | UDF_TEXT |
|---|---|
wsrde | CREATE FUNCTION GetSmallest(:A integer, :B Integer) RETURNS Integer AS BEGIN DECLARE :smallest INTEGER IF (:A < :B ) THEN SET :smallest = :A; ELSE SET :smallest = :B; END IF; RETURN :smallest; END |
Error Conditions
Condition | Error Message |
|---|---|
database_qualifier is a blank string | Please enter a valid database name. Database name cannot be a blank string |
udf_name is null | No argument or default value supplied. |
udf_name is a blank string | User-defined function name cannot be a blank string. |
psp_help_view
Returns the definition text of a view, from the current database or the specified database.
Syntax
call psp_help_view(['database_qualifier'], 'view_name')
Arguments
Parameter | Type | Default Value | Description |
|---|---|---|---|
Database_qual | VARCHAR(20) | Current database | Name of the database from which the details are to be obtained |
view_name | VARCHAR(255) | (no default value) | Name of the view whose definition text is required. Pattern matching is supported. |
Returned Result Set
Column Name | Data Type | Description |
|---|---|---|
DATABASE_QUALIFIER | VARCHAR (20) | Name of the database |
VIEW_TEXT | LONGVARCHAR | View definition text. |
Example
Assume that the following view exists for the Demodata sample database:
CREATE VIEW vw_Person (lastn,firstn,phone) AS
SELECT Last_Name, First_Name, Phone
FROM Person;
The following statement returns the definition text for view "vw_Person" in the Demodata database.
call psp_help_view(,'vw_Person')
or
call psp_help_view(,'vw_%')
Result Set
Database_Qualifier | VIEW_TEXT |
|---|---|
Demodata | SELECT "T1" ."Last_Name" ,"T1" ."First_Name" ,"T1" ."Phone" FROM "Person" "T1" |
Error Conditions
Condition | Error Message |
|---|---|
database_qualifier is a blank string | Please enter a valid database name. Database name cannot be a blank string. |
view_name is null | No argument or default value supplied. |
view_name is a blank string | View name cannot be a blank string. |
psp_indexes
Returns the list of indexes defined for the specified table. For each index, it also lists the index properties as persisted in the X$Index table.
Syntax
call psp_indexes(['table_qualifier'], ['table_name'])
Arguments
Parameter | Type | Default Value | Description |
|---|---|---|---|
table_qualifier | VARCHAR(20) | Current database | Name of the database from which the details are to be obtained |
table_name | VARCHAR(255) | (no default value) | Name of the table for whose indexes are to be obtained. Pattern matching is supported. |
Returned Result Set
Column Name | Data Type | Description |
|---|---|---|
TABLE_QUALIFIER | VARCHAR (20) | Name of the database |
TABLE_OWNER | VARCHAR (20) | Name of the owner of the primary key table |
TABLE_NAME | VARCHAR(255) | Name of the primary key table |
INDEX_NAME | VARCHAR(255) | Name of the index |
INDEX_TYPE | VARCHAR (20) | Type of the Index: primary, foreign, or normal |
COLUMN_NAME | VARCHAR(255) | Name of the column on which index is defined |
ORDINAL_POSITION | USMALLINT | Ordinal position of the index |
DUPLICATES_ALLOWED | CHAR(3) | Yes, if it is a duplicate index No, if it is not a duplicate index |
UPDATABLE | CHAR(3) | Yes, if the index is updatable No, if the index is not updatable |
CASE_SENSITIVE | CHAR(3) | Yes, if the index is case-sensitive No, if the index is not case-sensitive |
ASC_DESC | CHAR(1) | D, Descending A, Ascending |
NAMED_INDEX | CHAR(3) | Yes, if it is a named index No, if it is not a named index |
Example
call psp_indexes(,)
Result Set
Qual | TO | TN | IN | IT | CN | Opos | Dup | Up | Case | A/D | NI |
|---|---|---|---|---|---|---|---|---|---|---|---|
Demodata | Null | Billing | Student_ Trans | Normal Index | Student_ ID | 0 | No | Yes | No | A | Yes |
Demodata | Null | Billing | Student_ Trans | Normal Index | Transaction_ Number | 1 | No | Yes | No | A | Yes |
Demodata | Null | Billing | Student_ Trans | Normal Index | Log | 2 | No | Yes | No | A | Yes |
..... | |||||||||||
Legend: Qual = Table_qualifier; TO = Table_owner; TN = Table_name; IN = Index_name; IT = Index_type; CN = Column_name; Opos = Ordinal_position; Dup = Duplicates_allowed; UP = Updatable; Case = Case_sensitive; A/D = Asc_desc; NI = Named_index | |||||||||||
============
call psp_indexes('demodata', 'Dep%')
Result Set
Qual | TO | TN | IN | IT | CN | Opos | Dup | Up | Case | A/D | NI |
|---|---|---|---|---|---|---|---|---|---|---|---|
Demodata | Null | Dept | Building_ Room | Normal Index | Building_Name | 0 | Yes | Yes | Yes | A | Yes |
Demodata | Null | Dept | Building_ Room | Normal Index | Room_ Number | 1 | Yes | Yes | No | A | Yes |
Demodata | Null | Dept | Dept_Head | Normal Index | Head_ Of_Dept | 0 | No | Yes | No | A | Yes |
Demodata | Null | Dept | Dept_Name | Normal Index | Name | 0 | No | Yes | Yes | A | Yes |
Legend: Qual = Table_qualifier; TO = Table_owner; TN = Table_name; IN = Index_name; IT = Index_type; CN = Column_name; Opos = Ordinal_position; Dup = Duplicates_allowed; UP = Updatable; Case = Case_sensitive; A/D = Asc_desc; NI = Named_index | |||||||||||
Error Conditions
Condition | Error Message |
|---|---|
database_qualifier is a blank string | Please enter a valid database name. Database name cannot be a blank string. |
table_name is a blank string | View name cannot be a blank string. |
psp_pkeys
Returns the primary key information for the specified table, from the current database or the database specified.
Syntax
call psp_pkeys(['pkey_table_qualifier']'table_name')
Arguments
Parameter | Type | Default Value | Description |
|---|---|---|---|
pkey_table_qualifier | VARCHAR(20) | Current database | Name of the database from which the details are to be obtained |
table_name | VARCHAR(255) | (no default value) | Name of the table whose primary key information is requested. Pattern matching is supported |
Returned Result Set
Column Name | Data Type | Description |
|---|---|---|
TABLE_QUALIFIER | VARCHAR (20) | Name of the database |
TABLE_OWNER | VARCHAR (20) | Name of the owner of the primary key table |
TABLE_NAME | VARCHAR(255) | Name of the primary key table |
COLUMN_NAME | VARCHAR(255) | Name of the primary key column |
COLUMN_SEQ | USMALLINT | Sequence of the columns (a segmented index) |
PK_NAME | VARCHAR(255) | Name of the primary key |
Example
The following statement returns the information about the primary key defined on the 'pkeytest1' table:
CREATE TABLE pkeytest1
(
col1 int NOT NULL,
col2 int NOT NULL,
col3 VARCHAR(20) NOT NULL,
PRIMARY KEY(col1, col2),
UNIQUE(col3)
);
call psp_pkeys(,'pkeytest1');
Result Set
Table_ qualifier | Table_ owner | Table_ name | Column_ name | Column_ Seq | PK_ name |
|---|---|---|---|---|---|
'demodata' | Null | Pkeytest1 | Col1 | 0 | PK_col1 |
'demodata' | Null | Pkeytest1 | Col2 | 1 | PK_col1 |
Error Conditions
Condition | Error Message |
|---|---|
pkey_table_qualifier is a blank string | Please enter a valid database name. Database name cannot be a blank string. |
table_name is null | No argument or default value supplied. |
table_name is a blank string | Table name cannot be a blank string. |
psp_procedure_rights
Returns the list of procedure rights and corresponding information for the specified stored procedure, from the current database or the specified database. The stored procedure can be a trusted or a non-trusted one. See Trusted and Non-Trusted Objects.
Syntax
call psp_procedure_rights(['database_qualifier'], ['procedure_name'], ['user_name'])
Arguments
Parameter | Type | Default Value | Description |
|---|---|---|---|
database_qualifier | VARCHAR(20) | Current database | Name of the database from which the details are to be obtained |
procedure_name | VARCHAR(255) | (no default value) | Name of the procedure for which rights are specified. Pattern matching is supported. |
user_name | VARCHAR(255) | Current user | Name of the user for whom the list of procedure rights needs to be obtained. Pattern matching is supported. |
Returned Result Set
Column Name | Data Type | Description |
|---|---|---|
PROCEDURE_QUALIFIER | VARCHAR (20) | Name of the database |
PROCEDURE_OWNER | VARCHAR (20) | Name of the owner of the procedure |
USER_NAME (GRANTEE) | VARCHAR(255) | Name of the user |
PROCEDURE_NAME | VARCHAR(255) | Name of the procedure |
RIGHTS | VARCHAR(12) | One of the following values: ALTER EXECUTE Note that RIGHTS pertains only to procedures in a database that uses V2 metadata. |
Example
Assume that the following permissions exist for the Demodata sample database:
GRANT EXECUTE ON PROCEDURE Dept1_Proc TO John;
GRANT ALTER ON PROCEDURE Dept1_Proc TO Mary;
GRANT ALTER ON PROCEDURE Dept1_Proc TO John;
GRANT EXECUTE ON PROCEDURE Proc2 TO Mary;
GRANT ALTER ON PROCEDURE Proc2 TO Mary;
GRANT ALTER ON PROCEDURE MyProc TO Mary;
The following statement prints the permissions on the "Dept1_Proc" stored procedure for user "John."
call psp_procedure_rights(,'Dept1_Proc', 'John');
Result Set
Procedure_Qualifier | Procedure_owner | User_name | Procedure_name | Rights |
|---|---|---|---|---|
Demodata | Null | John | Dept1_Proc | ALTER |
Demodata | Null | John | Dept1_Proc | EXECUTE |
The following statement prints the permissions on the "Proc2" stored procedure for user "Mary."
call psp_procedure_rights('demodata', '%Pr%', 'M%')
Result Set
Procedure_Qualifier | Procedure_owner | User_name | Procedure_name | Rights |
|---|---|---|---|---|
Demodata | Null | Mary | MyProc | ALTER |
Demodata | Null | Mary | Proc2 | ALTER |
Demodata | Null | Mary | Proc2 | EXECUTE |
Error Conditions
Condition | Error Message |
|---|---|
database_qualifier is a blank string | Please enter a valid database name. Database name cannot be a blank string. |
procedure_name is a blank string | Procedure name cannot be a blank string. |
user_name is a blank string | User name cannot be a blank string. |
psp_procedure_rights called for a database with V1 metadata | View and Stored Procedure permissions are not supported for metadata version 1. |
psp_rename
Changes the name of a COLUMN, INDEX, FUNCTION, PROCEDURE, TABLE, TRIGGER or VIEW in the database to which your machine is currently connected.
Syntax
call psp_rename('object_name','new_name','object_type')
Arguments
Parameter | Type | Description |
|---|---|---|
object_name | VARCHAR(776) | The current name of the column, index, user-defined function, stored procedure, table, trigger or view. Object_name must be specified in a particular format depending on the type of object: • Column: table_name.column_name. • Index: table_name.index_name. • Function: function_name • Procedure: procedure_name • Table: table_name • Trigger: table_name.trigger_name • View: view_name |
new_name | VARCHAR(776) | A user-defined name for the object. The name must conform to the naming conventions for the type of object. See Naming Conventions in Zen Programmer’s Guide. |
object_type | VARCHAR(13) | The type of object being renamed. Object_type must be one of the following: COLUMN, INDEX, FUNCTION, PROCEDURE, TABLE, TRIGGER or VIEW. |
Example
The following statement renames stored procedure "checkstatus" to "eligibility" in the current database.
call psp_rename('checkstatus', 'eligibility', 'PROCEDURE')
Error Conditions
All errors returned from psp_rename use status code -5099. See -5099: Error condition pertaining to a stored procedure in Status Codes and Messages.
psp_stored_procedures
Returns the list of stored procedures and their corresponding information from the current database or the specified database.
Syntax
call psp_stored_procedures(['database_qualifier'], ['procedure_name'], ['procedure_type'])
Arguments
Parameter | Type | Default Value | Description |
|---|---|---|---|
Database_qualifier | VARCHAR(20) | Current database | Name of the database from which the details are to be obtained |
procedure_name | VARCHAR(255) | (no default value) | Name of the stored procedure whose information is required. Pattern matching is supported. |
procedure_type | VARCHAR(5) | (no default value) | 'SP' returns the stored procedures 'SSP' returns the system stored procedures (this type is currently not supported) |
Note: System stored procedures are defined in the internal PERVASIVESYSDB database, which is not displayed in Zen Control Center.
Returned Result Set
Column Name | Data Type | Description |
|---|---|---|
PROCEDURE_QUALIFIER | VARCHAR (20) | Name of the database |
PROCEDURE _OWNER | VARCHAR (20) | Name of the owner of the procedure |
PROCEDURE _NAME | VARCHAR(255) | Name of the procedure |
PROCEDURE_TYPE | VARCHAR(25) | Type of procedure. The types are STORED PROCEDURE or SYSTEM STORED PROCEDURE. |
NUM_INPUT_PARAMS | INT | Returns null, because SQLPROCEDURES returns null when executed against Zen DSN |
NUM_OUTPUT_PARAMS | INT | Returns null, because SQLPROCEDURES returns null when executed against Zen DSN |
NUM_RESULT_SETS | INT | Returns null, since SQLPROCEDURES returns null when executed against Zen DSN |
REMARKS | VARCHAR(255) | Remarks |
TRUSTEE | INTEGER | For V2 metadata, returns 0 for a trusted stored procedure and -1 for a nontrusted stored procedure. The TRUSTEE column is empty for V1 metadata. |
Example
Assume that the current database mydbase contains two stored procedures: myproc1 and myproc2. The following statement lists the information about them.
Call psp_stored_procedures(, ,)
Result Set
Qualifier1 | Owner1 | Name1 | Type1 | Num_ input _params | Num_ output_ params | Num_ result_ sets | Remarks | Trustee |
|---|---|---|---|---|---|---|---|---|
mydbase | Null | Myproc1 | Stored Procedure | Null | Null | Null | Null | |
mydbase | Null | Myproc2 | Stored Procedure | Null | Null | Null | Null | |
1The complete column name includes "procedure_" prepended to this name: Procedure_qualifier, procedure_owner, and so forth. | ||||||||
============
The following statement lists the information about the stored procedures in the PERVASIVESYSDB internal database.
call psp_stored_procedures('PERVASIVESYSDB', 'psp_u%', 'SP')
Result Set
Qualifier | Owner | Name | Type | Num_ input_ params | Num_ output_ params | Num_ result_ sets | Remarks | Trustee |
|---|---|---|---|---|---|---|---|---|
pervasivesystdb | Null | psp_udfs | Stored Procedure | Null | Null | Null | Null | |
pervasivesystdb | Null | psp_users | Stored Procedure | Null | Null | Null | Null | |
Note: For qualifier, owner, name, and type, the complete column name includes "procedure_" prepended to this name: Procedure_qualifier, procedure_owner, and so forth. | ||||||||
Error Conditions
Condition | Error Message |
|---|---|
database_qualifier is a blank string | Please enter a valid database name. Database name cannot be a blank string |
table_name is a blank string | Table name cannot be a blank string. |
procedure_type is a blank string | Procedure type cannot be a blank string. |
procedure_type is a value other than SP, SSP, or null | Procedure type can be SP, SSP, or null. |
psp_tables
Returns a list of tables along with their corresponding information, from the current database or the specified database.
Syntax
call psp_tables(['database_qualifier'], ['table_name'], ['table_type'])
Arguments
Parameter | Type | Default Value | Description |
|---|---|---|---|
Database_qualifier | VARCHAR(20) | Current database | Name of the database from which the details are to be obtained |
table_name | VARCHAR(255) | (no default value) | Name of the table whose information needs to be obtained. Pattern matching is supported. |
table_type | VARCHAR(20) | (no default value) | Must be one of the following: 'User table' returns only the user tables 'System table' returns all the system tables NULL returns all tables |
Returned Result Set
Column Name | Data Type | Description |
|---|---|---|
TABLE_QUALIFIER | VARCHAR (20) | Name of the database |
TABLE_OWNER | VARCHAR (20) | Name of the table owner |
TABLE_NAME | VARCHAR(255) | Name of the table |
TABLE_TYPE | VARCHAR (15) | System table - if the table is a system table User table - if the table has been created by any user |
REMARKS | VARCHAR(255) | Remarks |
FILE_LOCATION | VARCHAR(255) | Location where the file is saved |
Example
call psp_tables(,,)
Result Set
Table_ Qualifier | Table_ owner | Table_ name | Table_ Type | Remarks | File_ location |
|---|---|---|---|---|---|
Demodata | Null | X$file | System table | Null | File.ddf |
Demodata | Null | X$field | System table | Null | Field.ddf |
Demodata | Null | X$Attrib | System table | Null | Attrib.ddf |
Demodata | Null | Billing | User table | Null | Billing.mkd |
..... |
============
call psp_tables(, , 'user table')
Result Set
Table_ Qualifier | Table_ owner | Table_ name | Table_ Type | Remarks | File_ location |
|---|---|---|---|---|---|
Demodata | Null | Class | User table | Null | class.mkd |
Demodata | Null | Billing | User table | Null | Billing.mkd |
..... |
============
call psp_tables(, , 'system table')
Result Set
Table_ Qualifier | Table_ owner | Table_ name | Table_ Type | Remarks | File_ location |
|---|---|---|---|---|---|
Demodata | Null | X$file | System table | Null | File.ddf |
Demodata | Null | X$field | System table | Null | Field.ddf |
Demodata | Null | X$Attrib | System table | Null | Attrib.ddf |
..... |
Error Conditions
Condition | Error Message |
|---|---|
database_qualifier is a blank string | Please enter a valid database name. Database name cannot be a blank string. |
table_name is a blank string | Table name cannot be a blank string. |
table_type is a blank string | Table type cannot be a blank string. |
table_type is something other than 'system table,' 'user table,' or null | Table type can be system table, user table or null. |
psp_table_rights
Returns the list of table rights and corresponding information for the specified table, from the current database or the specified database.
Syntax
call psp_table_rights(['database_qualifier'], ['table_name'], ['user_name'])
Arguments
Parameter | Type | Default Value | Description |
|---|---|---|---|
Database_qualifier | VARCHAR(20) | Current database | Name of the database from which the details are to be obtained |
table_name | VARCHAR(255) | All tables | Name of the table for which rights have are specified. Pattern matching is supported. |
user_name | VARCHAR(255) | Current user | Name of the user for whom the list of table rights needs to be obtained. Pattern matching is supported. |
Returned Result Set
Column Name | Data Type | Description |
|---|---|---|
TABLE_QUALIFIER | VARCHAR (20) | Name of the database |
TABLE_OWNER | VARCHAR (20) | Name of the owner of the table |
USER_NAME (GRANTEE) | VARCHAR(255) | Name of the user |
TABLE_NAME | VARCHAR(255) | Name of the table |
RIGHTS | VARCHAR(12) | One of the following values: SELECT ALTER DELETE INSERT REFERENCES SELECT UPDATE |
Example
Assume that the following permissions exist for the Demodata sample database.
GRANT SELECT ON Dept TO John;
GRANT ALTER ON Dept TO John;
GRANT DELETE ON Dept TO John;
GRANT SELECT ON Class TO Mary;
GRANT ALTER ON Class TO Mary;
The following statement prints the table permissions on the "Dept" table for user "John" in the current database (Demodata).
call psp_table_rights(,'Dept', 'John');
Result Set
Table_Qualifier | Table_owner | User_name | Table_name | Rights |
|---|---|---|---|---|
Demodata | Null | John | Dept | ALTER |
Demodata | Null | John | Dept | DELETE |
Demodata | Null | John | Dept | SELECT |
============
Assume that user "Mary" is logged on the database. The following statement prints the table permissions on the "Class" table in the Demodata database for the current user (Mary).
call psp_table_rights('demodata', 'cl%', )
Result Set
Table_Qualifier | Table_owner | User_name | Table_name | Rights |
|---|---|---|---|---|
Demodata | Null | Mary | Class | SELECT |
Demodata | Null | Mary | Class | ALTER |
Error Conditions
Condition | Error Message |
|---|---|
database_qualifier is a blank string | Please enter a valid database name. Database name cannot be a blank string. |
table_name is a blank string | Table name cannot be a blank string. |
user_name is a blank string | User name cannot be a blank string. |
psp_triggers
Returns the list of triggers and their corresponding information from the current database or the specified database.
Syntax
call psp_triggers(['database_qualifier'], ['table_name'])
Arguments
Parameter | Type | Default Value | Description |
|---|---|---|---|
Database_qualifier | VARCHAR(20) | Current database | Name of the database from which the details are to be obtained |
table_name | VARCHAR(255) | All tables | Name of the table for which the trigger is defined. Pattern matching is supported. |
Returned Result Set
Column Name | Data Type | Description |
|---|---|---|
TRIGGER_QUALIFIER | VARCHAR (20) | Name of the database |
TRIGGER_OWNER | VARCHAR (20) | Name of the owner of the Trigger |
TABLE_NAME | VARCHAR(255) | Name of the table for which the trigger is defined. |
TRIGGER_NAME | VARCHAR(255) | Name of the trigger |
ISUPDATE | UTINYINT | Is set if it is an update trigger |
ISDELETE | UTINYINT | Is set if it is an delete trigger |
ISINSERT | UTINYINT | Is set if it an insert trigger |
ISAFTER | UTINYINT | Is set if the trigger action time is "after" |
ISBEFORE | UTINYINT | Is set if the trigger action time is "before" |
REMARKS | VARCHAR(255) | Remarks |
Example
Assume that the current database is mydbase. The following statement returns the list of triggers defined in the database:
CREATE TABLE A
(
col1 INTEGER,
col2 CHAR(255)
) ;
CREATE TABLE B
(
col1 INTEGER,
col2 CHAR(255)
) ;
CREATE TRIGGER Insert
AFTER INSERT ON A
FOR EACH ROW
INSERT INTO B VALUES
(NEW.col1, NEW.col2);
call psp_triggers(,);
Result Set
Trigger_ qualifier | Trigger_ owner | Table_ name | Trigger_ name | isupdate | isdelete | isinsert | isafter | isbefore | Remarks |
|---|---|---|---|---|---|---|---|---|---|
mydbase | Null | A | Insert | 0 | 0 | 1 | 0 | 0 | Null |
Error Conditions
Condition | Error Message |
|---|---|
database_qualifier is a blank string | Please enter a valid database name. Database name cannot be a blank string. |
table_name is a blank string | Table name cannot be a blank string. |
psp_udfs
Returns the list of user-defined functions (UDF) and their corresponding information from the current database or the specified database.
Syntax
call psp_udfs(['database_qualifier'], ['udf_name'])
Arguments
Parameter | Type | Default Value | Description |
|---|---|---|---|
Database_qualifier | VARCHAR(20) | Current database | Name of the database from which the details are to be obtained |
udf_name | VARCHAR(255) | All user-defined functions | Name of the udf whose details are needed. Pattern matching is supported. |
Returned Result Set
Column Name | Data Type | Description |
|---|---|---|
UDF_QUALIFIER | VARCHAR (20) | Name of the database |
UDF_OWNER | VARCHAR (20) | Name of the owner of the UDF |
UDF _NAME | VARCHAR(255) | Name of the UDF |
UDF_TYPE | VARCHAR(25) | Type of UDF (always set to 1) Special UDF types are not currently supported. |
NUM_INPUT_PARAMS | INT | Returns null, because SQLPROCEDURES returns null when executed against Zen DSN. |
NUM_OUTPUT_PARAMS | INT | Returns 1, because UDFs return only scalar values |
NUM_RESULT_SETS | INT | Returns 0, because UDFs do not return resultsets |
REMARKS | VARCHAR(255) | Remarks |
Example
Assume that the current database mydbase has two user-defined functions: calcinterest and factorial.
call psp_udfs(, )
Result Set
UDF_ qualifier | UDF_ owner | UDF_ name | Udf_ type | Num_ input_ params | Num_ output_ params | Num_ result_ sets | Remarks |
|---|---|---|---|---|---|---|---|
mydbase | Null | CalcInterest | 1 | Null | 1 | 0 | Null |
mydbase | Null | Factorial | 1 | Null | 1 | 0 | Null |
Error Conditions
Condition | Error Message |
|---|---|
database_qualifier is a blank string | Please enter a valid database name. Database name cannot be a blank string. |
udf_name is a blank string | User-defined function name cannot be a blank string. |
psp_users
Returns the list of users and the corresponding information from the current database or the specified database.
Syntax
call psp_users(['database_qualifier'], ['group_name'], ['user_name'])
Arguments
Parameter | Type | Default Value | Description |
|---|---|---|---|
database_qualifier | VARCHAR(20) | Current database | Name of the database from which the details are to be obtained |
group_name | VARCHAR(255) | All groups (if group_name is null) | Name of the group used to return the user information. Pattern matching is supported. If group name is specified (i.e. if it is not NULL), only users belonging to the same group will be returned. |
user_name | VARCHAR(255) | All users (if user_name is null) | Name of the user. Pattern matching is supported. |
Returned Result Set
Column Name | Data Type | Description |
|---|---|---|
DATABASE_QUALIFIER | VARCHAR(20) | Name of the database |
GROUP_ID | USMALLINT | Group ID of the group to which user belongs |
GROUP_NAME | VARCHAR(255) | Name of the group to which user belongs |
USER_ID | USMALLINT | ID of the user |
USER_NAME | VARCHAR(255) | Name of the user |
Example
Assume that current database mydbase has users John, Mary, and Michael, and groups DevGrp and DevGrp1.
call psp_users(, , )
Result Set
Database_qualifier | Group_Id | Group_Name | User_Id | User_Name |
|---|---|---|---|---|
Demodata | 1 | DevGrp | 3 | John |
Demodata | 2 | DevGrp1 | 1 | Mary |
Demodata | 1 | DevGrp | 4 | Michael |
============
call psp_users(, 'Devgrp', )
Result Set
Database_qualifier | Group_Id | Group_Name | User_Id | User_Name |
|---|---|---|---|---|
Demodata | 1 | DevGrp | 3 | John |
Demodata | 2 | DevGrp | 4 | Michael |
Error Conditions
Condition | Error Message |
|---|---|
database_qualifier is a blank string | Please enter a valid database name. Database name cannot be a blank string. |
user_name is null | User name cannot be a null. |
group_name is a blank string | Group name cannot be a blank string. |
psp_view_rights
Returns the list of list of view rights and corresponding information for the specified view, from the current database or the specified database. The view can be a trusted or a non-trusted one. See Trusted and Non-Trusted Objects.
Psp_view_rights applies only to a database using V2 metadata.
Syntax
call psp_view_rights(['database_qualifier'], ['view_name'], ['user_name'])
Arguments
Parameter | Type | Default Value | Description |
|---|---|---|---|
database_qualifier | VARCHAR(20) | Current database | Name of the database from which the details are to be obtained |
view_name | VARCHAR(255) | All views (if view_name is null) | Name of the view for which rights are specified. Pattern matching is supported. |
user_name | VARCHAR(255) | Current user (if user_name is null) | Name of the user for whom the list of view rights needs to be obtained. Pattern matching is supported. |
Returned Result Set
Column Name | Data Type | Description |
|---|---|---|
VIEW_QUALIFIER | VARCHAR (20) | Name of the database |
VIEW_OWNER | VARCHAR (20) | Name of the owner of the view |
USER_NAME (GRANTEE) | VARCHAR(255) | Name of the user |
VIEW_NAME | VARCHAR(255) | Name of the view |
RIGHTS | VARCHAR(12) | One of the following values: ALTER DELETE INSERT SELECT UPDATE |
Example
Assume that the following permissions exist for the Demodata sample database:
GRANT SELECT ON VIEW vw_Dept TO John;
GRANT ALTER ON VIEW vw_Dept TO John;
GRANT DELETE ON VIEW vw_Dept TO John;
GRANT SELECT ON VIEW vw_Class TO Mary;
GRANT ALTER ON VIEW vw_Class TO Mary;
GRANT SELECT ON VIEW vw_Class TO Prakesh;
The following statement prints the view permissions on the "vw_Dept" view for user "John."
call psp_view_rights(,'vw_Dept', 'John');
Result Set
View_Qualifier | View_owner | User_name | View_name | Rights |
|---|---|---|---|---|
Demodata | Null | John | vw_Dept | ALTER |
Demodata | Null | John | vw_Dept | DELETE |
Demodata | Null | John | vw_Dept | SELECT |
============
Assume that user "Mary" is logged on the database. The following statement prints the view permissions on all views in the sample database Demodata for the current user (Mary).
call psp_view_rights('demodata', , )
Result Set
View_Qualifier | View_owner | User_name | View_name | Rights |
|---|---|---|---|---|
Demodata | Null | Mary | vw_Class | ALTER |
Demodata | Null | Mary | vw_Class | SELECT |
============
The following statement prints the view permissions on the "vw_Class" view for user "Mary."
call psp_view_rights('demodata', 'vw_C%', 'Mary')
Result Set
View_Qualifier | View_owner | User_name | View_name | Rights |
|---|---|---|---|---|
Demodata | Null | Mary | vw_Class | ALTER |
Demodata | Null | Mary | vw_Class | SELECT |
Error Conditions
Condition | Error Message |
|---|---|
database_qualifier is a blank string | Please enter a valid database name. Database name cannot be a blank string |
view_name is a blank string | View name cannot be a blank string. |
user_name is a blank string | User name cannot be a blank string. |
psp_procedure_rights called for a database with V1 metadata | View and stored procedure permissions are not supported for V1 metadata. |
psp_views
Returns the list of views along with their corresponding information, from the current database or from the database specified.
Syntax
call psp_views(['database_qualifier'], ['view_name'])
Arguments
Parameter | Type | Default Value | Description |
|---|---|---|---|
Database_qualifier | VARCHAR(20) | Current database | Name of the database from which the details are to be obtained |
view_name | VARCHAR(255) | (no default value) | Name of the view whose information is required. Pattern matching is supported. |
Returned Result Set
Column Name | Data Type | Description |
|---|---|---|
VIEW_QUALIFIER | VARCHAR (20) | Name of the database |
VIEW_OWNER | VARCHAR (20) | Name of the owner of the view |
VIEW_NAME | VARCHAR(255) | Name of the view |
REMARKS | VARCHAR(255) | Remarks |
TRUSTEE | INTEGER | For V2 metadata, returns 0 for a trusted view and -1 for a non-trusted view. The TRUSTEE column is empty for V1 metadata. |
Example
Assume that the following view exists for a V2 metadata database named Demodata2.
CREATE VIEW vw_Person (lastn,firstn,phone) WITH EXECUTE AS 'Master' AS
SELECT Last_Name, First_Name,Phone FROM Person;
The following statement prints the list of views in the current database, which is Demodata2.
call psp_views(, ,);
Result Set
View_Qualifier | View_Owner | View_Name | Remarks | Trustee |
|---|---|---|---|---|
Demodata | Null | Vw_Person | Null | 0 |
============
The following statement prints the view information for the "vw_Person" view in the Demodata database.
call psp_views('demodata', 'vw_P%')
Result Set
View_Qualifier | View_Owner | View_Name | Remarks | Trustee |
|---|---|---|---|---|
Demodata | Null | Vw_Person | Null |
Error Conditions
Condition | Error Message |
|---|---|
database_qualifier is a blank string | Please enter a valid database name. Database name cannot be a blank string |
view_name is a blank string | Table name cannot be a blank string. |
Last modified date: 08/05/2026