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

Know the version of the DB

Database Version - SQL Server/ Sybase

Select @@Version

Print @@Version

Apart from the SQL Server Version, It also provides processor architecture, build date, and operating system for the current installation

The above information can also be retrieved from the following stored procedure

Exec xp_msver

Keywords: Version, xp_msver, System Stored Procedure

Selecting Database users

Selecting Database users


The following queries will be returning the user names

Select User

-- This displays the current user
Select User_Name()

Select Current_User

-- This displays the user belongint to ID 2
Select User_Name(2)

-- Displays all users for that DB
Select uid, name from sysusers order by uid

sysusers contain the information of all users

Monitoring SQL SErver with SP_WHO

MONITORING SQL SERVER WITH SP_WHO

Exec sp_who

will provide information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine

Use the login to filter results for a particular user
Exec sp_who 'Login21'

Maximum Connections for SQL Server

All About Connections

@@MAX_Connections Returns the maximum number of simultaneous user connections allowed on an instance of SQL Server. The number returned is not necessarily the number currently configured

Select @@MAX_Connections as Max_Connections

No of attempted connections for SQL Server

Select @@Connections as TotalLoginAttempts

This will include either successful or unsuccessful since SQL Server was last started.

You can also check the same using sp_monitor System procedure

Exec sp_monitor