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:
B.ROUTINE_TYPE AS OBJ_TYPE
, A.SPECIFIC_NAME AS OBJ_NAME
, A.CHARACTER_MAXIMUM_LENGTH AS STRING_MAX_LENGTH
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
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.
- BOL – here.