I want to search a string called "Insert into dba.t_table" in all procedures on sql anywhere 12.0
Currently I am using
select proc_name, * from sysprocedure where source like '%INSERT INTO dba.t_table%'
which gives only 5 rows. But sometimes we write code such as
INSERT INTO dba.t_table
So there is a whole line space after
select proc_name, * from sysprocedure where source like '%dba.t_table%'
But this gives 95 rows. (too much)
Any other easy and accurate way to search this?
If you are concerned with identifying whitespace, you may use SIMILAR TO instead of LIKE, something like (the untested):
WHERE source SIMILAR TO '%INSERT INTO[[:whitespace:]]+dba.t_table%'
BTW: You should also make sure that the "preserve_source_format option" is set, otherwise the "source" column may be NULL. If it is not set, you can search within column "proc_defn", which will contain the code in possibly reformatted way.