Developer Reference > Data Access Methods > SQL Engine Reference > System Stored Procedures
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.
psp_columns
psp_column_attributes
psp_column_rights
psp_fkeys
psp_groups
psp_help_sp
psp_help_trigger
psp_help_udf
psp_help_view
psp_indexes
psp_pkeys
psp_procedure_rights
psp_rename
psp_stored_procedures
psp_tables
psp_table_rights
psp_triggers
psp_udfs
psp_users
psp_view_rights
psp_views
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 be executed.
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_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 does not display 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: 10/31/2023