How to list stored procedure parameter list and its return type

[NK7]

Hi Can anybody help me to list the stored procedure parameters and return type in sql server 2005/2000 for a given stored procedure. For ex: Parameter_Name Data_Type Length --------------------------------- kesavan

[Cristian Amarie]

Here it is a version (displays from master database the parameters name, type and length for sp_addserver stored procedure):

-- SQL 2000/2005
if exists (select * from syscolumns where id = object_id('sp_addserver'))
begin
select
name
, type_name(xusertype)
, length
from syscolumns where id = object_id('sp_addserver')
end

-- SQL 2005
if exists (select * from sys.all_parameters where object_id = object_id('sp_addserver'))
begin
select
name
, type_name(user_type_id)
, max_length
from sys.all_parameters where object_id = object_id('sp_addserver')
end

Cristian

Nuclear launch detected