Toad World® Forums

Retrieving list of stored procedures in Object Browser


#1

We downloaded Toad for Sybase 2.0.1.209 to see if it would be a viable replacement for SQLProgrammer as SQLProgrammer does not seem to work in a Windows 7 environment and we are migrating off of XP. During my testing I have noticed that when I open the Object Browser and try to display a list of the stored procedures in one database, it will take minutes to retrieve that list and cause locks on the database. It will eventually tell me that there are too many objects to display. There are 3344 stored procedures in the database. I have no issues retrieving this list in SQLProgrammer’s Access Manager and it retrieves the list in seconds. Is this a known issue and if so, will it be fixed in a future release? Or is it something that can be fixed in our Toad setup (thru Options or something)? Thanks for any help.


#2

Hello Steve,

Please have a look at Tools -> Options under Database -> SQL Dictionary. In that section there is a folder called Db.CommonSQL with an entry called ListProcedures. Here you can see the SQL that Toad is executing to get this information. You can also modify the SQL in this window to fit your needs. Please let me know if this helps.

Thanks.


#3

Thanks. I modified the SQL and it worked.


#4

Hello SteveK,

May I ask what was causing the issue? I would like to review the SQL and make sure we are doing this correctly.

Thanks.


#5

I modified my SQL so it wouldn’t use the sysobjects table. I didn’t need the mode values (unchained, chained, etc) that the original query retrieved, and wasn’t concerned about the other columns retrieved other than the procedure name and creation date. I didn’t do any research to see if maybe the original SQL was not using a correct index or something on the retrieval. My SQL is:

SELECT so.name AS name,
so.id as groupnumber,
user_name(so.uid) AS owner,
so.crdate AS crdate,
so.id,
‘anymode’ as mode
FROM sysobjects so
WHERE so.type = ‘P’
ORDER BY so.name asc

One problem I do notice is that if I change the SQL, it doesn’t save it permanently. The next time I open Toad, the SQL has reverted back to its default script. Does it store this syntax in a file under the install directory that I might not have write permissions to?


#6

Hello SteveK,

Thanks for the update, we will work to optimize the SQL. The new SQL is saved to the application data directory which can be found by going to Help -> About and clicking on the “Application Data Directory” link. The file is called SqlDictionary.xml.

Thanks again.


#7

BTW, you should have complete write access to this directory so if the file is not being read on restart please let me know and we will create an issue for it.

Thanks.