List of all stored procedures that have no parameters
I want to find out which stored procedures in the database have no parameters. I tried this but not sure:
1) Concatenating tables sys.all_parameters
and sys.all_objects
:
select
ao.name,ao.type, ao.type_desc
from
sys.all_parameters pa
left outer join
sys.all_objects ao
on pa.object_id = ao.object_id
where
pa.name like ''
and
ao.type not in ('FN','AF','FS')
2) From the table information_schema.parameters
:
select *
from
information_schema.parameters
where
parameter_mode not in ('in', 'out', 'inout')
3) From information_schema.parameters
:
select *
from
information_schema.parameters
where
parameter_name like ''
However, I'm not entirely sure if they are correct. Is there something direct?
Maybe something like:
select * from sys.procedures where xtype = 'P' and has_parameters=0
+3
source to share