Script to retrive parameters for stored procedure

,

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).

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_type	char(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

Rate

Share

Share

Rate