Display Stored Procedure Parameters

drop proc hx_ProcParamInfo
go

create proc hx_ProcParamInfo
AS

/* Display any stored procedures using input and output parameters.
Robert Vallee 08/27/2001
rvallee@hybridx.com
input: none
output: Table format.
description: I took the view, PARAMETERS that ships with SQL 2000 and 
cleaned it up a bit.


*/
SELECT     
PROC_NAME = o.name, 
ORDINAL_POSITION = c.colid, 
PARAMETER_MODE = CONVERT(nvarchar(10), 
CASE 
WHEN c.colid = 0 THEN 'OUT' 
WHEN ColumnProperty(c.id, c.name, 'IsOutParam')= 1 THEN 'IN/OUT' 
ELSE 'IN' 
END), 
IS_RESULT = CONVERT(nvarchar(10), 
CASE 
WHEN c.colid = 0 THEN 'YES'
ELSE 'NO' 
END), 
PARAMETER_NAME = c.name, 
DATA_TYPE = spt_dtp.LOCAL_TYPE_NAME, 
CHARACTER_MAXIMUM_LENGTH = CONVERT(int, OdbcPrec(c.xtype, c.length, c.xprec) + spt_dtp.charbin), 
CHARACTER_OCTET_LENGTH = CONVERT(int, spt_dtp.charbin + CASE WHEN spt_dtp.LOCAL_TYPE_NAME IN ('nchar', 'nvarchar', 'ntext') THEN 2 * OdbcPrec(c.xtype, c.length, c.xprec) 
ELSE OdbcPrec(c.xtype, c.length, c.xprec) END), 
COLLATION_NAME = c.collation, 
CHARACTER_SET_NAME = CONVERT(sysname,CASE WHEN spt_dtp.LOCAL_TYPE_NAME IN ('char', 'varchar', 'text') THEN a_cha.name 
WHEN spt_dtp.LOCAL_TYPE_NAME IN ('nchar', 'nvarchar','ntext') THEN N'Unicode' 
ELSE NULL END), 
NUMERIC_PRECISION = c.xprec, 
NUMERIC_PRECISION_RADIX = spt_dtp.RADIX, 
NUMERIC_SCALE = c.scale, 
DATETIME_PRECISION = spt_dtp.SQL_DATETIME_SUB

FROM         sysobjects o, syscolumns c JOIN
                      master.dbo.spt_datatype_info spt_dtp ON c.xtype = spt_dtp.ss_dtype AND (spt_dtp.ODBCVer IS NULL OR
                      spt_dtp.ODBCVer = 2) AND (spt_dtp.AUTO_INCREMENT IS NULL OR
                      spt_dtp.AUTO_INCREMENT = 0), master.dbo.syscharsets a_cha
WHERE     o.xtype IN ('P', 'FN', 'TF', 'IF') AND o.id = c.id AND (c.number = 1 OR
 (c.number = 0 AND o.xtype = 'FN')) AND permissions(o.id) != 0 AND 
  a_cha.id = isnull(CONVERT(tinyint, CollationProperty(c.collation, 'sqlcharset')), 
CONVERT(tinyint, ServerProperty('sqlcharset')))
ORDER BY o.name,c.colid