Need a query which gets all the Parameters from Procedure

Question

Hi I Used below Query But it is getting data of Both parameters which are allowing nulls and Not allowing nulls .Please Help this SELECT p.name AS Parameter t.name AS [Type] FROM sys.procedures sp JOIN sys.parameters p ON sp.object_id= p.object_id JOIN sys.types t ON p.system_type_id = t.system_type_id WHERE sp.name =yourStoredprocedurename

Chakri P · Answer

yes

Chakri P · Answer

Noit's Not working

Biswa Pujarini Mohapatra · Answer

SELECT SCHEMA_NAME(SCHEMA_ID) AS [Schema] SO.name AS [ObjectName] SO.Type_Desc AS [ObjectType (UDF/SP)] P.parameter_id AS [ParameterID] P.name AS [ParameterName] TYPE_NAME(P.user_type_id) AS [ParameterDataType] P.max_length AS [ParameterMaxBytes] P.is_output AS [IsOutPutParameter] FROM sys.objects AS SO INNER JOIN sys.parameters AS P ON SO.OBJECT_ID = P.OBJECT_ID WHERE SO.OBJECT_ID IN ( SELECT OBJECT_ID FROM sys.objects WHERE TYPE IN ('P''FN')) ORDER BY [Schema] SO.name P.parameter_id try this

Riyaz Akhtar · Answer

what do you mean by null and not null parameter ? Do you mean optional parameter?

Chakri P · Answer

No i want null parameters as well as Not null parameters

Biswa Pujarini Mohapatra · Answer

do you want only not null parameters ?

Chakri P · Answer

Yes but it is getting all the Parameters irrespective of null and Not Null

Riyaz Akhtar · Answer

try this: sp_help [your sp name] it will return two recordset 2nd recordset will have all parameters.

Chakri P · Answer

Thanks for the Sugesstion But it is not working for all procedures Can You Please tell the Exact query

Biswa Pujarini Mohapatra · Answer

try this select * from information_schema.parameters where specific_name='your_procedure_name'