jump to navigation

Extract user-defined function or stored procedure source code or definition: Oracle vs SQL Server vs DB2 vs MYSQL vs Informix vs Sybase : count number of occurences March 30, 2010

Posted by sendtoshailesh in Uncategorized.
Tags:
trackback

Digging dictionaries or system catalog is always interesting. For my one of project, I had to extract the source code or definition of user-defined function or stored procedure based on given keyword or pattern. this was on multiple database product. counting the number of occurrences in body of each function and procedures was also main stuff in the code.

For Oracle :

Stored Function search:

select schema_name, function_name,count(no_of_occur_in_this_line) total_no_of_occur

from (

select owner schema_name,name function_name,line line_no,

(LENGTH(upper(text)) – LENGTH(REPLACE(upper(text), upper(‘search_keyword’), ”)))

/ LENGTH(‘search_keyword’)  no_of_occur_in_this_line, text line_of_code

from all_source a

where type=’FUNCTION’ and upper(text) like ‘%’||upper(‘Search_Keyword’)||’%’)

group by schema_name, function_name;

Stored Procedure search:

select schema_name,procedure_name,count(no_of_occur_in_this_line) total_no_of_occur

from (

select owner schema_name,name procedure_name,line line_no,

(LENGTH(upper(text)) – LENGTH(REPLACE(upper(text), upper(‘search_keyword’), ”)))

/ LENGTH(‘search_keyword’) no_of_occur_in_this_line, text line_of_code

from all_source a

where type=’PROCEDURE’ and upper(text) like ‘%’||upper(‘Search_Keyword’)||’%’)

group by schema_name,procedure_name;

———————————————————————————————————————-

For DB2:

Stored Function search:

select ROUTINESCHEMA schema_name,ROUTINEMODULENAME function_name,

(LENGTH(upper(text)) – LENGTH(REPLACE(upper(text), upper(‘search_keyword’), ”)))

/ LENGTH(‘search_keyword’)  no_of_occur, text source_code

from SYSCAT.ROUTINES

where ROUTINETYPE=’F’ and upper(text) like ‘%’||upper(‘search_keyword’)||’%’;

Stored Procedure search:

select ROUTINESCHEMA schema_name,ROUTINEMODULENAME procedure_name,

(LENGTH(upper(text)) – LENGTH(REPLACE(upper(text), upper(‘search_keyword’), ”)))

/ LENGTH(‘search_keyword’)  no_of_occur, text source_code

from SYSCAT.ROUTINES

where ROUTINETYPE=’P’ and upper(text) like ‘%’||upper(‘search_keyword’)||’%’;

—————————————————————————————————————————–

For SQL Server:

Stored Function search:

Keyword occurrence count in function body

SELECT all_functions.object_id,all_functions.function_name,all_functions.schema_name,

all_functions.function_type,

(LEN(upper(f_source.definition)) – LEN(REPLACE(upper(f_source.definition), upper(‘Search_Keyword’), ”))) / LEN(‘Search_Keyword’) no_of_occurr

from (SELECT object_id,name AS function_name ,SCHEMA_NAME(schema_id) AS schema_name,

type_desc function_type

FROM sys.objects

WHERE type_desc LIKE ‘%FUNCTION%’) all_functions,sys.sql_modules f_source

Where (all_functions.function_name like ‘%’+upper(‘Search_Keyword’)+’%’

or

exists (Select ‘X’

from sys.sql_modules sm where all_functions.object_id=sm.object_id

and sm.definition like ‘%’+upper(‘Search_Keyword’)+’%’))

and all_functions.object_id=f_source.object_id;

List of functions where keyword found

SELECT object_id,function_name,schema_name,function_type from (

SELECT object_id,name AS function_name ,SCHEMA_NAME(schema_id) AS schema_name,type_desc function_type

FROM sys.objects

WHERE type_desc LIKE ‘%FUNCTION%’) all_functions

Where all_functions.function_name like ‘%’+upper(‘search_keyword’)+’%’

or

exists (Select ‘X’

from sys.sql_modules sm where all_functions.object_id=sm.object_id

and sm.definition like ‘%’+upper(‘search_keyword’)+’%’);

 
 
Stored Procedure search:
  
Keyword occurrence count in procedure body
  
SELECT all_procedures.object_id,all_procedures.procedure_name,all_procedures.schema_name, 

all_procedures.procedure_type, 

(LEN(upper(p_source.definition)) - LEN(REPLACE(upper(p_source.definition), upper('Search_Keyword'), ''))) / LEN('Search_Keyword') no_of_occurr
 from (SELECT object_id,name AS procedure_name ,SCHEMA_NAME(schema_id) AS schema_name, 
type_desc procedure_type
 FROM sys.objects 
WHERE type_desc LIKE '%PROCEDURE%') all_procedures,sys.sql_modules p_source
 Where (all_procedures.procedure_name like '%'+upper('Search_Keyword')+'%' 
or exists (Select 'X'
 from sys.sql_modules sm where all_procedures.object_id=sm.object_id 
and sm.definition like '%'+upper('Search_Keyword')+'%'))
 and all_procedures.object_id=p_source.object_id;  
List of procedures where keyword found
    
SELECT object_id,procedure_name,schema_name,procedure_type from ( 

SELECT object_id,name AS procedure_name , 

SCHEMA_NAME(schema_id) AS schema_name, type_desc procedure_type 

FROM sys.objects  

WHERE type_desc LIKE '%PROCEDURE%') all_procedures  

Where all_procedures.procedure_name like '%'+upper('search_keyword')+'%'  

or  exists (Select 'X' 

from sys.sql_modules sm where all_procedures.object_id=sm.object_id
 and sm.definition like '%'+upper('search_keyword')+'%');

-----------------------------------------------------------------------------------------------
For Informix:

Stored Function search:
  
select owner schema_name,procname function_name, 

(LENGTH(upper(data)) - LENGTH(REPLACE(upper(data), upper('Search_Keyword'), '')))

/ LENGTH('Search_Keyword')  no_of_occur, data source_code

from sysprocedures a,sysprocbody b

where a.procid=b.procid and b.datakey='T' and upper(data) like '%'||upper('Search_Keyword')||'%'
----------------------------------------------------------------------------------------------------
For MYSQL:


Stored Function search:
  
SELECT ROUTINE_SCHEMA SCHEMA_NAME, ROUTINE_NAME,ROUTINE_DEFINITION source_code,

(LENGTH(upper(ROUTINE_DEFINITION)) - LENGTH(REPLACE(upper(ROUTINE_DEFINITION), upper('search_keyword'), '')))

/ LENGTH('search_keyword')  no_of_occur

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_TYPE='FUNCTION' 

and (ROUTINE_NAME like concat(concat('%',upper('search_keyword')),'%')
 or ROUTINE_DEFINITION like concat(concat('%',upper('search_keyword')),'%'));



Stored Procedure search:
  
SELECT ROUTINE_SCHEMA SCHEMA_NAME, ROUTINE_NAME,ROUTINE_DEFINITION source_code,

(LENGTH(upper(ROUTINE_DEFINITION)) - LENGTH(REPLACE(upper(ROUTINE_DEFINITION), upper('search_keyword'), '')))

/ LENGTH('search_keyword')  no_of_occur

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_TYPE='PROCEDURE'

and (ROUTINE_NAME like concat(concat('%',upper('search_keyword')),'%')

or ROUTINE_DEFINITION like concat(concat('%',upper('search_keyword')),'%'));

 

--------------------------------------------------------------------------------------

For Sybase:

Stored Function search:
  
select creator schema_name,proc_name function_name, 

(LENGTH(upper(proc_defn)) - LENGTH(REPLACE(upper(proc_defn), upper('Search_Keyword'), '')))

/ LENGTH('Search_Keyword')  no_of_occur, proc_defn source_code

from sysprocedure

where upper(proc_defn) like '%'||upper('Search_Keyword')||'%'

 

Stored Procedure search:

select creator schema_name,proc_name procedure_name,

(LENGTH(upper(proc_defn)) - LENGTH(REPLACE(upper(proc_defn), upper('Search_Keyword'), '')))

/ LENGTH('Search_Keyword')  no_of_occur, proc_defn source_code

from sysprocedure

where upper(proc_defn) like '%'||upper('Search_Keyword')||'%'
 
About these ads

Comments»

1. Daniel - December 11, 2013

Waw! Tanks!!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 81 other followers

%d bloggers like this: