Zen v16 | System Stored Procedures

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.

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))

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')

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
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
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: 11/04/2024