Accessing the source code of the database executable objects

All major database servers allow the creation of executable objects, such as functions or stored procedures, using some type of SQL extension language, such as PLSQL in Oracle or T-SQL in SQL Server, so that we can create applications to access and manipulate data in a more sophisticated way. In this article I will show how we can access the source code of these objects from any program using the catalog views of three of the most popular RDBMS, MySQL, Oracle and SQL Server.

Obtaining the list of code objects

The code objects you can find in the database are basically stored procedures, functions and triggers. In Oracle there are also packages, which are groupings of various stored procedures and functions in a single block of code.

MySQL

The catalog views are in the INFORMATION_SCHEMA schema, in particular, to list the procedures and functions you have to use the ROUTINES view, whereas to list the triggers you have the TRIGGERS view.

The most relevant fields of the ROUTINES view are the following:

• ROUTINE_SCHEMA: Indicates the schema to which the object belongs.
• ROUTINE_NAME: The name of the object.
• ROUTINE_TYPE: The object type, PROCEDURE or FUNCTION.
• DATA_TYPE: The type of data that the object returns, if it is a function.

The TRIGGERS view has a similar structure:

• TRIGGER_SCHEMA: The schema to which the trigger belongs.
• TRIGGER_NAME: The name of the trigger.
• EVENT_MANIPULATION: The event that fires it.
• EVENT_OBJECT_SCHEMA: The schema of the table to which the trigger is attached.
• EVENT_OBJECT_TABLE: The name of the table.

With a statement like this we can list all the stored procedures, functions and triggers that we have in the database:

select ROUTINE_SCHEMA,
ROUTINE_NAME,
ROUTINE_TYPE,
DATA_TYPE as TYPE,
NULL as PARENT_SCHEMA,
NULL as PARENT_NAME
from INFORMATION_SCHEMA.ROUTINES
union
select TRIGGER_SCHEMA,
TRIGGER_NAME,
'TRIGGER',
EVENT_MANIPULATION,
EVENT_OBJECT_SCHEMA,
EVENT_OBJECT_TABLE
from INFORMATION_SCHEMA.TRIGGERS;

SQL Server

SQL Server also uses the INFORMATION_SCHEMA.ROUTINES view, with a structure very similar to MySQL. To obtain a list of stored procedures and functions we can use the same fields. However, there is no TRIGGERS view. We can list the triggers using a query like this:

select st.name as [trigger schema],
t.name as [trigger name],
te.type_desc as [event],
s.name as [table schema],
o.name as [table name]
from sys.triggers t
join sys.objects o on t.parent_id = o.object_id
join sys.objects ot on t.object_id = ot.object_id
join sys.schemas s on o.schema_id = s.schema_id
join sys.schemas st on ot.schema_id = st.schema_id
join sys.trigger_events te on t.object_id = te.object_id

Oracle

In Oracle we have a single view to list all objects that contain executable code, the ALL_PROCEDURES view. These are the most relevant fields:

• OWNER: Schema where the object is defined.
• OBJECT_NAME: The name of the object.
• PROCEDURE_NAME: Where the different procedure or function names of a PACKAGE are listed.
• OBJECT_TYPE: The type of object, which can be PROCEDURE, FUNCTION, PACKAGE or TRIGGER.

For objects of type PACKAGE we will obtain a record for each of the procedures or functions contained in the package. If we just want to list the package name, we can filter the query using a PROCEDURE_NAME IS NULL filter:

select OWNER,
OBJECT_NAME,
OBJECT_TYPE
from all_procedures
where PROCEDURE_NAME IS NULL

Accessing the source code of procedures, functions, and triggers

Once we know what executable objects are in the database, the next step is to know how we can access their source code. This can be useful to you when you try to implement editors or some kind of search for a particular text string or regular expression through all the code contained in the database or one or more particular schemas.

MySQL

The source code for a stored procedure or function can be found in the ROUTINE_DEFINITION field of the ROUTINES view in INFORMATION_SCHEMA. The problem is that in this field is the body of the procedure or function, but nothing is indicated about the calling parameters. To find them you have to use the INFORMATION_SCHEMA.PARAMETERS view, whose most important fields are as follows:

• SPECIFIC_SCHEMA: Schema where the procedure or function is located.
• SPECIFIC_NAME: Name of the procedure or function.
• ORDINAL_POSITION: Indicates the position of the parameter in the object calling.
• PARAMETER_MODE: Indicates whether it is an input or output parameter.
• PARAMETER_NAME: Name of the parameter.
• DTD_IDENTIFIER: The parameter definition with its size and precision.

With this data and those found in the INFORMATION_SCHEMA.ROUTINES view we can build the CREATE PROCEDURE or CREATE FUNCTION statement to have the complete source code of the object. This statement, for example, obtains the parameters of one of the stored procedures of the schema sakila, which usually comes as an example in MySQL:

select r.ROUTINE_TYPE,
r.ROUTINE_SCHEMA,
r.ROUTINE_NAME,
p.PARAMETER_MODE,
p.PARAMETER_NAME,
p.DTD_IDENTIFIER
from information_schema.routines r,
information_schema.parameters p
WHERE r.ROUTINE_SCHEMA = p.SPECIFIC_SCHEMA
AND r.ROUTINE_NAME = p.SPECIFIC_NAME
AND (r.ROUTINE_SCHEMA,r.ROUTINE_NAME) = ('sakila','film_in_stock')
ORDER BY p.ORDINAL_POSITION

SQL Server

In SQL Server the source code for stored procedures and functions is also found in the ROUTINE_DEFINITION field of INFORMATION_SCHEMA.ROUTINES, and in this RDBMS it does include the complete statement, with CREATE PROCEDURE and its parameters, so it is not necessary to use any other view to found them.

In any case, if you need information about the parameters, you can also use the view INFORMATION_SCHEMA.PARAMETERS, as in MySQL. However, in this case you do not have the DTD_IDENTIFIER field, so, if you want to know the size and precision of a parameter, you have to use the fields DATA_TYPE, to obtain the data type, CHARACTER_MAXIMUM_LENGTH, for text fields and NUMERIC_PRECISION and NUMERIC_SCALE, for numeric types.

Another way to access the source code is to use the OBJECT_DEFINITION function of T-SQL. You must pass as a parameter the identifier of the object, which can be obtained with the OBJECT_ID function, passing the object name as a text string, in the form: schema.object_name:

SELECT OBJECT_DEFINITION(OBJECT_ID(‘schema.procname’))

This function can be useful for obtaining the code in very large procedures, with which the view can return an incomplete source code.

Oracle

Finally, let's see how to get the source code in an Oracle database. This is the simplest of all cases. To do this, there is a view of the system catalog called ALL_SOURCE, which contains a row for each line of code. This system allows code of a much larger size than those any type of data can store, but we lose the possibility of searching for text that span more than one line of code, having to first rebuild the entire text somewhere before doing the search.

The fields that you can find of interest in the view are:

• OWNER: Name of the schema that contains the object.
• NAME: Name of the object.
• LINE: The line number, essential for ordering the text.
• TEXT: The text of the line.

As in the previous case, the code includes the entire definition of the object, including the complete SQL statement that creates it. But if, for any reason, you want to obtain the parameters, you can use the ALL_ARGUMENTS view. Here are the most useful fields of it:

• OWNER: The schema to which the object belongs.
• OBJECT_NAME: Name of the procedure or function.
• PACKAGE_NAME: If the object is included in a PLSQL package, it contains the name of that package.
• ARGUMENT_NAME: Name of the parameter.
• POSITION: Parameter order number.
• DATA_TYPE: Data type of the parameter.
• IN_OUT: Indicates whether the parameter direction is input or output.
• CHAR_LENGTH: Parameter size for text types.
• DATA_PRECISION and DATA_SCALE: Field size for numeric types.