Display any stored procedures using input and output parameters. I took the view, PARAMETERS that ships with SQL 2000 and cleaned it up a bit.
2001-08-22
729 reads
Display any stored procedures using input and output parameters. I took the view, PARAMETERS that ships with SQL 2000 and cleaned it up a bit.
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