|
|
Recently while working on an application that heavily uses Stored Procedures, I had to often open up SQL Server Management Studio/ Visual Studio Server Explorer to physically check the parameters of a Stored Procedure. This became cumbersome at times since the procs were in different databases and I wish there were a query that returned me the Stored Procedure parameters, as soon as I supply it with the stored procedure name.
If you have been looking out for something similar, here’s what I came up with:
SELECT parm.name AS Parameter,
typ.name AS [Type]
FROM sys.procedures sp
JOIN sys.parameters parm ON sp.object_id = parm.object_id
JOIN sys.types typ ON parm.system_type_id = typ.system_type_id
WHERE sp.name = 'aspnet_Membership_GetUsers'
OUTPUT:
I am a happy man now :)
Did you like this post?
|
|
||
|
|
|
|
|
subscribe via rss |
|
subscribe via e-mail |
|
print this post |
|
follow me on twitter |
comments
5 Responses to "Find Out The Parameters Of A Stored Procedure in SQL Server 2005/2008"great man
but you can use this query like
SELECT parm.name AS Parameter,
typ.name AS [Type]
FROM sys.objects sp
JOIN sys.parameters parm ON sp.object_id = parm.object_id
JOIN sys.types typ ON parm.system_type_id = typ.system_type_id
WHERE sp.name = 'aspnet_Membership_GetUsers'
so it will useful for both procedure and functions also
Best Regards,
Raj Acharya
thanks mate. was looking for something like this and found this useful.
I also needed something that would work for sql2000 as well and came across the below query that works for 2000 and 2005.
SELECT PARAMETER_MODE, PARAMETER_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_NAME='PROC NAME'
cheers
Prem
The second result set will also give the same
EXEC sp_help 'procedure name'
Nice Post,
But in case you have created your own data types that are i.e. datetime your query would return more than one rows.
In that case I run the following query
SELECT parm.name AS Parameter,
typ.name AS [Type]
FROM sys.procedures sp
JOIN sys.parameters parm ON sp.object_id = parm.object_id
JOIN sys.types typ ON parm.user_type_id = typ.user_type_id
WHERE sp.name = 'aspnet_Membership_GetUsers'
Thanks!
Thanks Dimitris!
Post a Comment