Hey all! Welcome to, or welcome back, to SQL Something!
Today we're taking a quick look at how to list all Stored Procedures (and Functions) in a database. As per usual, I needed to do this at some point for my job and so I'm sharing what I found. :-)
In order to get the information we need we will be querying information_schema.routines as follows:
SELECT ROUTINE_NAME as 'Stored Procedure',
ROUTINE_CATALOG as 'Database Name',
ROUTINE_SCHEMA as 'Database Schema',
ROUTINE_TYPE as 'Object Type (PROCEDURE/FUNCTION)',
ROUTINE_DEFINITION as 'Stored Procedure Definition',
CREATED as 'Date Created',
LAST_ALTERED as 'Date Last Altered'
FROM information_schema.routines with (nolock)
WHERE ROUTINE_TYPE = 'PROCEDURE'
ORDER BY ROUTINE_NAME
The above will list all Store Procedures for the database that you run the query on. Very helpful! :-)
Now you'll notice the WHERE clause specifies the PROCEDURE type only. If we wanted info on Functions as well as Stored Procedures, we can remove this clause.
If we wanted info on Functions only and wanted some additional info like what value the Function returns, we tweak the query as follows:
SELECT ROUTINE_NAME as 'Function Name',
ROUTINE_CATALOG as 'Database Name',
ROUTINE_SCHEMA as 'Database Schema',
ROUTINE_TYPE as 'Object Type (PROCEDURE/FUNCTION)',
ROUTINE_DEFINITION as 'Function Definition',
DATA_TYPE as 'Return Value Type',
CREATED as 'Date Created',
LAST_ALTERED as 'Date Last Altered'
FROM information_schema.routines with (nolock)
WHERE ROUTINE_TYPE = 'FUNCTION'
ORDER BY ROUTINE_NAME
DATA_TYPE returns the data type of the Function's return value or it returns TABLE if it's a table valued function.
DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to
politely correct or comment as you see fit. Your feedback is always
welcomed. :-)
