how to get parameterless procedure ?

  • Thread starter Thread starter Lloyd Dupont
  • Start date Start date
L

Lloyd Dupont

I already try something like that:
SELECT INFORMATION_SCHEMA.ROUTINES.SPECIFIC_NAME,
COUNT(INFORMATION_SCHEMA.PARAMETERS.PARAMETER_NAME) AS Expr1
FROM INFORMATION_SCHEMA.ROUTINES INNER JOIN
INFORMATION_SCHEMA.PARAMETERS ON
INFORMATION_SCHEMA.ROUTINES.SPECIFIC_NAME =
INFORMATION_SCHEMA.PARAMETERS.SPECIFIC_NAME
GROUP BY INFORMATION_SCHEMA.ROUTINES.SPECIFIC_NAME


unfortunately this gave me all the procedure with parameter (and the number
of parameters), but none of the procedure WITHOUT parameters do appear.
any clues on how to get procedure without parameters ?
 
well I found that, which gives me also the one with 0 parameter, then I have
to cull out the other while reading the result set.
any idea how to cull them out in the select ?
///-----------------------
SELECT INFORMATION_SCHEMA.ROUTINES.SPECIFIC_NAME AS Script,
COUNT(INFORMATION_SCHEMA.PARAMETERS.PARAMETER_NAME)
AS NumParam
FROM INFORMATION_SCHEMA.ROUTINES LEFT OUTER JOIN
INFORMATION_SCHEMA.PARAMETERS ON
INFORMATION_SCHEMA.ROUTINES.SPECIFIC_NAME =
INFORMATION_SCHEMA.PARAMETERS.SPECIFIC_NAME
GROUP BY INFORMATION_SCHEMA.ROUTINES.SPECIFIC_NAME
 
Hi Lloyd,

Add HAVING clause at the end:
HAVING COUNT(INFORMATION_SCHEMA.PARAMETERS.PARAMETER_NAME) = 0
(if you need only parameterless)
 
Thanks Miha, works great !

Miha Markic said:
Hi Lloyd,

Add HAVING clause at the end:
HAVING COUNT(INFORMATION_SCHEMA.PARAMETERS.PARAMETER_NAME) = 0
(if you need only parameterless)
Click to expand...
 
Back
Top