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
No comments:
Post a Comment