Stored Procedures & Parameters

SQL Server / Sybase Stored Procedures & Parameters

-- Sybase Syntax to Select Parameters used in All Available Stored Procedures
select * from SYSPROCPARM

-- SQL Server Syntax to Select Parameters used in All Available Stored Procedures
select * from sys.parameters


-- Sybase Syntax to Select All Available Stored Procedures
select * from sysprocedure

-- SQL Server Syntax to Select All Available Stored Procedures
select * from sys.procedures


-- To get the list of both input and output parameters used in every
stored procedure use the following syntax

-- Sybase
select sp.proc_name, spa.parm_name from sysprocedure sp, SYSPROCPARM spa
where sp.proc_id = spa.proc_id
order by sp.proc_name

--SQL Server
select sp.name, spa.name from sys.procedures sp, sys.parameters spa
where sp.object_id = spa.object_id
order by sp.name


-- Procedures and their Input Parameters

-- Sybase
select sp.proc_name, spa.parm_name from sysprocedure sp, SYSPROCPARM spa
where sp.proc_id = spa.proc_id
and parm_mode_in = 'Y'
order by sp.proc_name


-- SQL Server
select sp.name, spa.name from sys.procedures sp, sys.parameters spa
where sp.object_id = spa.object_id
and spa.is_output = 0
order by sp.name



-- Procedures and their Output Parameters
-- Sybase
select sp.proc_name, spa.parm_name from sysprocedure sp, SYSPROCPARM spa
where sp.proc_id = spa.proc_id
and parm_mode_out = 'Y'
order by sp.proc_name

-- SQL Server
select sp.name, spa.name from sys.procedures sp, sys.parameters spa
where sp.object_id = spa.object_id
and spa.is_output = 1
order by sp.name