April 28, 2008 at 7:34 am
I am trying to pull information about all the user created stored procedures in a database with a query. I have successfully pulled all the procs with parameters, but I want to result set to also contain the stored procedures without parameters. I can't quite get it. Any help would be appreciated:
SELECTprocs.name as ProcName,
params.name as ParameterName,
types.name as ParamType,
params.max_length,
params.precision,
params.scale,
params.is_output
FROMsys.procedures procs
LEFT OUTER JOINsys.all_parameters params
ONprocs.object_id = params.object_id
LEFT OUTER JOINsys.types types
ONparams.system_type_id = types.system_type_id
WHEREparams.user_type_id = types.user_type_id
ANDprocs.is_ms_shipped = 0
ORDER BYprocname,
params.parameter_id
April 28, 2008 at 7:46 am
The first part of your WHERE clause is effectively turning your second OUTER JOIN into an INNER JOIN. Try putting this condition as a join predicate instead.
John
April 28, 2008 at 7:56 am
How do I add a join predicate?
I tried to add another join clause but the tables are already contained in a join clause. I tried aliasing the tables to new names but the query timed out....
Thanks.
April 28, 2008 at 7:59 am
Check the reply I just added to your other thread (where you originally asked about regexes). That should give you what you need for this query as well.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 28, 2008 at 8:00 am
...
LEFT OUTER JOIN sys.types types
ON params.system_type_id = types.system_type_id
AND params.user_type_id = types.user_type_id
WHERE procs.is_ms_shipped = 0
ORDER BY procname,
params.parameter_id
John
April 28, 2008 at 8:05 am
Awesome, thanks for the help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply