c# - Retrieving stored procedure script and parameters separately

Question

I want to access the stored procedure script using C#.

I used EXEC sp_HelpText STOREDPROCNAME. This is working fine.

Is there any way to retrieve stored procedure query and the stored procedure parameter separately??

ex: I need:

delete from [dbo].[tblTransactions] where [ID] = @ID

and

@ID numeric(18,0)

separately.

How to do this?

Answer 1

The simplest way is to ask the db directly with SqlCommandBuilder.DeriveParameters

Retrieves parameter information from the stored procedure specified in the SqlCommand and populates the Parameters collection of the specified SqlCommand object.

There is also

exec [dbname].[sys].[sp_procedure_params_rowset] @procedure_name=N'uspblabla'

the source of which you can see via sp_helptext sp_procedure_params_rowset.