Systems Engineering and RDBMS

Getting a list of all parameters for procs and functions

Posted by decipherinfosys on March 19, 2009

While preparing an admin report for a client, wrote a very simple SQL to list out all the stored procedures and functions in a schema along with all of their parameters, their  data types, in/out parameter characteristics and other pertinent details.  Here is the SQL:

SELECT
B.ROUTINE_TYPE                AS OBJ_TYPE
, A.SPECIFIC_NAME                    AS OBJ_NAME
, A.SPECIFIC_SCHEMA
, A.PARAMETER_NAME
, A.PARAMETER_MODE
, A.IS_RESULT
, A.DATA_TYPE
, A.CHARACTER_MAXIMUM_LENGTH        AS STRING_MAX_LENGTH
, A.CHARACTER_SET_NAME
, A.NUMERIC_PRECISION
, A.NUMERIC_SCALE
FROM INFORMATION_SCHEMA.PARAMETERS AS A
INNER JOIN INFORMATION_SCHEMA.ROUTINES AS B
ON A.SPECIFIC_NAME = B.SPECIFIC_NAME
AND A.SPECIFIC_SCHEMA = B.SPECIFIC_SCHEMA
WHERE B.ROUTINE_TYPE IN (‘FUNCTION’, ‘PROCEDURE’)
ORDER BY      A.SPECIFIC_NAME
, A.ORDINAL_POSITION

Have used the Information_Schema views instead of sys.objects and sys.parameters since that is the recommended approach from MSFT.  The SQL above uses two information_schema views:  Information_Schema.Routines and Information_Schema.Parameters – you can look up the BOL links and add other columns as well for your report if you need to.  This is a quick way of preparing a SSRS report and taking a look at the parameter definitions of the stored procedures and functions in the schema.  Some other columns of interest might be IS_DETERMINISTIC, SQL_DATA_ACCESS and ROUTINE_DEFINITION.

Resources:

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

 
%d bloggers like this: