How can I extract parameters from a stored procedure in SQL Server 2005?

Question

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

How to do it in .net?

Answer 1

You can run the following SQL query in SQL Server 2005. Of course, you can invoke the same query using the class SqlCommand.

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 system views procedures, parametersand typesinclude other interesting stored procedures and parameter information. This request is just a choice.

Answer 2

SqlCommandBuilder.DeriveParameter, (VB.NET) Static (#), SqlCommand: DeriveParameter MSDN. SqlCommand, , SqlCommand.Parameters.