How Can I Extract parameters from Stored Procedure in SQL Server 2005?

Question

Given a Stored procedure, I want to extract the parameter from it.

How can I do this in .net?

Answer 1

You should use the SqlCommandBuilder.DeriveParameter, which is Shared (VB.NET) or Static (C#) to which you pass the SqlCommand: DeriveParameter on MSDN. You just have to create a SqlCommand, setting the name of the stored procedure, call this method and look at the SqlCommand.Parameters property.

Answer 2

You can run the following SQL query in SQL Server 2005. You can of course call the same query using the SqlCommand class.

SELECT
    p.name,
    p.object_id,
    pm.parameter_id,
    pm.name AS parameter_name,
    pm.system_type_id AS parameter_system_type_id,
    pm.max_length AS parameter_max_length,
    t.name AS type_name
FROM sys.procedures p
JOIN sys.parameters pm ON p.object_id = pm.object_id
JOIN sys.types t ON pm.system_type_id = t.system_type_id
WHERE p.name = 'sprocName'

Of course, the procedures, parameters and types system views contain other interesting stored procedure and parameter information as well. This query is just a selection.