These procedures are installed into sybsystemprocs on sybase and into master on sql server. All procedure names start with "sp__" (two underscores). This naming convention ensures that the procedures, when run, will be available from any database and that, when they are run, the database context will be the current database. This naming convention also ensures that there are no naming conflicts with Sybase's internal procedures. Thus, if you are in the statsdb database, you can get the space used in statsdb by running sp__dbspace.
Most of these procs can be passed the parameter @dont_format='Y' to produce unformatted output. Normally these procs will attempt to produce output that is readable in 80 column (isql/osql) mode. This is done by truncating some fields (dbname, username etc). If you are using a graphical query analyzer you can pass the @dont_format parmeter in and it will show all fields (no column truncation).
sp_who @dont_format='Y'
These procedures have been tested under
SQL SERVER 2000
SQL SERVER 2005
The library is distributed in two ways. The Generic Enterprise Manager software incldues a copy and a full featured user interface to install the procedures. GEM also uses these procedures extensively - the console creates numerous reports based on these procedures that can be viewed using your web browser. You can also download these procedures as a standalone package.
The primary interface to install this library is to use the configure.pl program. configure.pl is a perl script that requires DBI and either DBD::Sybase (or DBD::ODBC if you are on windows). You can also use the older - unsupported .bat/.sh files that are shipped with the distribution but let me repeat - they are not supported. The .bat files require two parameters SERVERNAME and PASSWORD (the installSQLSVR.bat script only requires SERVERNEAME - it uses native authentication).
configure.sh - my original shell script installer
installSQLSVR.bat
installSYBASE12.bat
installSYBASE15.bat
installSYBASE_MDA.bat - must change to mda procedure directory
You are, of course, required to know the sa password to the server to install. Well thats not quite true - on sql server i believe that if you enter an incorrect password it will attempt to install using native windows authentication. Note that, on sybase, if you use the default size for sybsystemprocs, you might encounter space problems and might be required to expand your database.
configure.pl prompts for all information needed to install. As system databases will be modified by these procedures, it is suggested that you dump that database before you load the procedures.
|
sp_depends: Better version of sp_depends |
|
sp_help: Better sp_help |
|
sp_helpdb: Database Information |
|
sp_helpdevice: Break down database devices into a nice report |
|
sp_helpgroup: List groups in database by access level |
|
sp_helpindex: Shows indexes by table |
|
sp_helpsegment: Segment Information |
|
sp_helpprotect: Simple Protection Info for the database |
|
sp_helptext: Show comments with line splits ok |
|
sp_helpuser: Lists users in current database by group (includes aliases) |
|
sp_lock: Lock information |
|
sp_syntax: Works on any procedure to give you syntax |
|
sp_who that fits on a page |
SYSTEM ADMINISTRATOR PROCEDURES
|
sp_lock: Blocking processes. |
|
sp_dumpdevice: Summary of current database space information. |
|
sp_dumpdevice: Listing of Dump devices |
|
sp_diskdevice: Listing of Disk devices |
|
sp_helpdbdev: Show how Databases use Devices |
|
sp_helplogin: Show logins and remote logins to server |
|
sp_helpmirror:Shows mirror information, discover broken mirrors |
|
sp_segment:Segment Information |
|
sp_serverServer summary report (very useful) |
|
sp_statGive basic server performance information (loops) |
|
sp_vdevno:Who's who in the device world |
DBA PROCEDURES
|
sp_badindex: list badly formed indexes (allow nulls) or those needing statistics |
|
sp_collist:ist all columns in database |
|
sp_find_missing_indexFinds keys that do not have associated index |
|
sp_flowxchart: Makes a flowchart of procedure nesting |
|
sp_groupprotect:Permission info by group |
|
sp_indexspace: Space used by indexes in database |
|
sp_id: Gives information on who you are and which db you are in |
| |
sp_noindex: list of tables without indexes. |
|
sp_helpcolumn: show columns for given table |
|
sp_helpdefault: list defaults (part of objectlist) |
|
sp_helpobject: list objects |
|
sp_helpproc: list procs (part of objectlist) |
|
lsp_helprule: ist rules (part of objectlist) |
|
sp_helptable: list tables (part of objectlist) |
|
sp_helptrigger: list triggers (part of objectlist) |
| |
lsp_helpview :list views (part of objectlist) |
|
sp_objprotect: Permission info by object |
|
sp_read_write: list tables by # procs that read, # that write, # that do both |
|
sp_trigger: Useful synopsis report of current database trigger schema |
|
sp__who - filtered for only active processes |
AUDIT PROCEDURES
|
sp_auditsecurity: Security Audit On Server |
|
sp_audit: Audit Current Database For Potential Problems |
|
sp_checkey: Generate script for referential integrity problems (uses key info from sp_foreignkey) |
REVERSE ENGINEERING PROCEDURES
|
sp_revalias: get alias generation script for current database |
|
sp_revsb:get database generation script for server |
|
gsp_revdevice: get device generation script for server |
|
sp_revgroup: get group generation script for current database |
|
sp_reindex: get index generation script for current database |
|
sp_relogin: get login generation script for server |
|
sp_remirror: get mirror generation script for current database |
|
sp_revsegment:get segment generation script for current database |
|
sp_revtable: get table generation script for current database |
|
sp_getuser: get user generation script for current database
|