Script to retrive parameters for stored procedure

IF OBJECT_ID('_Get_ParamsForProc')IS NOT NULL 
DROP PROCEDURE  _Get_ParamsForProc
GO
Create proc _Get_ParamsForProc
@Procname varchar(128),
@Like char(1) = null
as

/*
This stored procedure will retrieve list of parameters for specified stored procedure.
To check similar names,like 'myproc_xxx', pass any char in the second parameter(optional).

Created by Eva Zadoyen
05/09/2002
**********
use pubs
go
exec _Get_ParamsForProc 'random_password' ,'y'

Results:

PROC_NAME             PARAMETER      DATA_TYPE  IS OUTPUT 
---------------------------------------------------------
random_password v.1   @len int NO
random_password v.1   @password_typechar(7) NO

*********************************************************

Possible usage - create parameters dynamically in the calling script and/or application (?)- haven't tried yet.
*/if @Like is not null 
  select @Like = '%'

select rtrim(convert(varchar (128),o.name))  + ' v.' + convert(varchar(5),C.NUMBER) PROC_NAME , rtrim(c.name) PARAMETER , 
rtrim(convert(varchar (50),d.type_name) + 
case when d.oledb_data_type = 129 /*DBTYPE_STR*/ or d.oledb_data_type = 128 /*DBTYPE_BYTES*/          then '(' + convert(varchar (10),coalesce(d.column_size,c.length)) + ')'
     when d.oledb_data_type = 130 /*DBTYPE_WSTR*/  then '(' +  convert(varchar(10), coalesce(d.column_size,c.length/2)) + ')'
     else'' 
end ) DATA_TYPE,
case when c.isoutparam =1 
     then 'YES' 
     else 'NO'
end as "IS OUTPUT"

from sysobjects o 
inner join sysobjects od on od.id = o.id
left outer join syscolumns c on o.id = c.id and o.type = 'P'
left outer join master.dbo.spt_provider_types d on c.xtype = d.ss_dtype

 
where c.length = case when d.fixlen > 0 then d.fixlen else c.length end
and o.name LIKE (@procname + @like)

ORDER BY o.name,c.NUMBER