Query for Stored Procedure parameter details.

Query for Stored Procedure parameter details.

This is just a real quick tip for collecting parameter details for all user defined functions and stored procedures.  I've find this very helpful when I take on a new customer, and 'inherit' a database that someone else has created.  This just helps me to analyze all of the objects with their parameter details.

Take a look, let me know what you think.

     /*
     Return procedures and functions with parameter details.  */

     SELECT 
         SCHEMA_NAME(SCHEMA_ID) [Schema], 
          so.name [ObjectName],
         CASE WHEN so.Type_Desc = 'SQL_STORED_PROCEDURE' THEN 'Procedure'
                  WHEN so.Type_Desc = 'SQL_SCALAR_FUNCTION' THEN 'Function' END [ObjectType],
        p.name [Parameter],
        TYPE_NAME(p.user_type_id) [ParameterDataType]
     FROM 
        sys.objects so INNER JOIN sys.parameters p
           ON so.OBJECT_ID = p.OBJECT_ID
     WHERE 
        so.OBJECT_ID IN (
                        SELECT OBJECT_ID 
FROM sys.objects
WHERE TYPE IN ('P','FN') )
     ORDER BY 
       [Schema], 
       so.name

Take a look at each of these topics for more details:

  sys.objects - http://msdn.microsoft.com/en-us/library/ms190324(v=sql.110).aspx
  sys.parameters - http://msdn.microsoft.com/en-us/library/ms176074(v=sql.110).aspx