function search

[former_SQLA_member1694914]

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

[dhkom]

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.

[justin_willey]

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.

[VolkerBarth]

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