Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in August and this forum will be retired.

Hello Everyone! ASA 17.0.11.6800

I have created user function in database. I use this function in many places in other functions, procedures, triggers... Now I need to replace this function with another one. Is there any option I could search and find all object where I use this function? This would be much of help.

Regards

Tomaz

asked 24 Aug '23, 09:33

Tomaz's gravatar image

Tomaz
166121220
accept rate: 0%


Wouldn't it be way easier to use ALTER FUNCTION to change the existing function's behaviour, and automatically each existing caller/user of that function gets the new behaviour? (Note: If you have used that function for computed columns, they might get re-computated.)


AFAIK, there's no builtin tool to do a fulltext-search within the database catalog for a particular name/value, but you can easily check the contents of the "xyz_def" and/or "source" column of system tables like SYSPROCEDURE/SYSTRIGGER/SYSEVENT/SYSVIEW or column definitions in SYSTABCOL for the desired text. I'm quite sure there are also several questions on that here, try to search for SYSPROCEDURE or the like.

Another brute-force option would be to unload the database schema only (via DBUNLOAD -n) and use within the according reload.sql file...

I guess most database developers use their favoured source code control system to maintain their scripts, and that's where I would do such searches...

permanent link

answered 24 Aug '23, 10:16

Volker%20Barth's gravatar image

Volker Barth
40.4k365553826
accept rate: 34%

We have been determining "where used" for years (decades?) by text-searching "reload.sql". This approach has become more complicated since "COMMENT TO PRESERVE FORMAT ON PROCEDURE ..." has been emitted into reload.sql files. Now every "where used" instance is found twice. FWIW - I wish there was a "dbunload" option to unload preserved format code only.

This said, there is the same text search capability in SQL Central - the "binoculars" button in the toolbar. If we weren't such creatures of habit, we would probably find this works better.

It helps to name choose names with text-search-where-used in mind. I.e. Such that your text search doesn't find too much of what you are not interested in.

permanent link

answered 29 Aug '23, 10:22

dharrel's gravatar image

dharrel
280121223
accept rate: 0%

SQL Central could really use a tag system to allow you to associate all sorts of objects with overlapping groupings. The problem with the Folders feature is that each folder can only contain a single type of object.

(31 Aug '23, 09:43) Justin Willey
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×28
×24
×5

question asked: 24 Aug '23, 09:33

question was seen: 350 times

last updated: 31 Aug '23, 09:44