|This tip was submitted by Sameer
Many of us of might have experienced that it takes ridiculously long time
to retrieve the list of stored procedures from database, when you are creating a stored
procedure-based datawindow or when you choose the 'Objects->Procedure Syntax' menu
option in PowerBuilder's database admin. painter. And this happens even with a moderately
Here is what one can do to speed up the response from server in this case. It also
serves as a good example of how one can customize Sybase's interface to Sybase/SQL Server
databases to advantage.
PowerBuilder uses a no. of stored procedures to communicate with the databases SQL
Server and SYBASE System 10/11. In case of Sybase System 10/11 they reside in the
Sybsystemprocs database. One can either change one or more of these stored procedures or
override them by having a procedure with the same name in the user-database he/she
generally works with. The changed version of the stored procedure has to go under his/her
own login name or the dbo.
In particular, the Sybase-defined stored procedure 'sp_pbX0proc' brings the list of all
stored procedures from the databases residing on the server (where X = 4, 5 depending on
PowerBuilder's major version). It actually pulls the list of all stored procs from system
databases Master and Sybsystemprocs along with the current user database; and this is what
makes it take such a lot of time. Usually, when you are in development you are not much
bothered about system stored procs from system databases Master and Sybsystemprocs. Hence
you may want to modify the Sybase procedure sp_pb40proc to refer only to the currently
connected database, for faster access. Following is the changed version of this procedure
which will retrieve the list within no time on most of the installations:
create proc sp_pb40proc @procid int = NULL,
@procnumber smallint = NULL as
if @procid = null
select o.id, o.name, o.uid, user_name(o.uid), 1, '1'
from dbo.sysobjects o
where o.type = 'P'
select name, type, length, colid from dbo.syscolumns
where (id = @procid and number = @procnumber)
Similarly, the procedure 'sp_pbX0table' retrieves the list of tables from a database in
the database painter. Many times one doesn't want to see the development versions of
actual tables created by various other developers in the development database. If this no.
is very large instead of scanning the huge list every time for actual (dbo-owned) or your
tables, you can customize this PB-procedure by adding following WHERE criteria in the
where(user_name(o.uid) in ("dbo", "") )
It will then retrieve only your or dbo's tables in the database painter.