pulling information about stored procedures query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

pulling information about stored procedures query

Author

Topic

chedderslam
Posting Yak Master

223 Posts

Posted - 2008-04-28 : 09:36:07

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:

SELECT procs.name as ProcName,
params.name as ParameterName,
types.name as ParamType,
params.max_length,
params.precision,
params.scale,
params.is_output
FROM sys.procedures procs
LEFT OUTER JOIN sys.all_parameters params
ON procs.object_id = params.object_id
LEFT OUTER JOIN sys.types types
ON params.system_type_id = types.system_type_id

WHERE params.user_type_id = types.user_type_id
AND procs.is_ms_shipped = 0
ORDER BY procname,
params.parameter_id

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2008-04-28 : 09:38:07

it's your where clause...
remove it and see what happens

quote:
Originally posted by chedderslam

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:

SELECT procs.name as ProcName,
params.name as ParameterName,
types.name as ParamType,
params.max_length,
params.precision,
params.scale,
params.is_output
FROM sys.procedures procs
LEFT OUTER JOIN sys.all_parameters params
ON procs.object_id = params.object_id
LEFT OUTER JOIN sys.types types
ON params.system_type_id = types.system_type_id

WHERE params.user_type_id = types.user_type_id
AND procs.is_ms_shipped = 0
ORDER BY procname,
params.parameter_id

--------------------
keeping it simple...

chedderslam
Posting Yak Master

223 Posts

Posted - 2008-04-28 : 09:46:11

No, that's not it. Removing the:
WHERE params.user_type_id = types.user_type_id

causes the result set to contain rows for every parameters as every data type, i.e. each parameter shows up over and over as a different data type.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2008-04-28 : 09:52:27

i tried your code and i get the routines without the parameters....

quote:
Originally posted by chedderslam

No, that's not it. Removing the:
WHERE params.user_type_id = types.user_type_id

causes the result set to contain rows for every parameters as every data type, i.e. each parameter shows up over and over as a different data type.

--------------------
keeping it simple...

chedderslam
Posting Yak Master

223 Posts

Posted - 2008-04-28 : 09:59:10

The original query or something different?

Could you post what you are actually running, mabye I've messed up somewhere and don't see it.

Thanks.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2008-04-28 : 10:29:40

[code]
SELECT procs.name as ProcName,
params.name as ParameterName,
types.name as ParamType,
params.max_length,
params.precision,
params.scale,
params.is_output
FROM sys.procedures procs
LEFT OUTER JOIN sys.all_parameters params
ON procs.object_id = params.object_id
LEFT OUTER JOIN sys.types types
ON params.system_type_id = types.system_type_id

WHERE --params.user_type_id = types.user_type_id and
procs.is_ms_shipped = 0
ORDER BY procname,
params.parameter_id
[/code]

--------------------
keeping it simple...

chedderslam
Posting Yak Master

223 Posts

Posted - 2008-04-28 : 10:38:23

I have it now. Here's what I came up with:

SELECT procs.name as ProcName,
params.name as ParameterName,
types.name as ParamType,
params.max_length,
params.precision,
params.scale,
params.is_output
FROM sys.procedures procs
LEFT OUTER JOIN sys.all_parameters params
ON procs.object_id = params.object_id
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

Thank you for the help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-28 : 10:49:32

Maybe this?

SELECT	*
FROM	INFORMATION_SCHEMA.PARAMETERS

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources