I have collected many customized System stored procedures from a multitude of sources. In all cases I've tried to preserve the author's name where due. If you see a missing reference where credit is due, please don't hesitate to email me.
Click here for the full list of all Custom Stored procs I have right now. The ones I believe are most useful are described in detail below.
dump tran sybsystemprocs with truncate_only
go
use sybsystemprocs
go
if exists (select * from sysobjects where name = "sp_name" and type = 'P')
drop proc sp_name
go
create procedure sp_name
as
...(sql statements)
go
if object_id('sp_name') is not null
begin
print '<<< Created procedure dbo.sp_name >>>'
grant execute on dbo.sp_name to public
end
else
begin
print '<<< Failed creating proc dbo.sp_name >>>'
end
go
Each is deployed by calling a line like this:
isql -Usa -S[server] -P[sa password] -i [sp_name.sql]
sp_block.sql:
This procedure is very similar to sp_lock except that it provides more
information and prints out the name of locked tables that are within the
current database. It also lists any blocked processes and who they are
blocked by. The use of multiple 'select' and 'print' statements was
avoided in order to use this in the current version of PowerBuilder's DBA
Painter (in PB, print is treated like raiserror and each select is treated
as a separate result set)
Usage : sp_block [database-name]
sp_blocker.sql:
This procedures identifies all spid's that are
blocking other spid's, but are not themselves blocked.
Usage : sp_blocker (no parameters)
sp_describe.sql
This procedure is a nicely formatted sp_help
Usage : sp_describe [table-name]
sp_devspace.sql
This procedure calculates neatly the total device space in use for all
database devices.
Usage : sp_devspace (no parameters)
sp_findmaxvdevno.sql
This stored procedure simply finds the maximum available vdevno
for device creation. Its essentially one ugly SQL statement that i
probably saved so I wouldn't have to type it ever again... :-)
Usage : sp_findmaxdevno (no parameters)
sp_freeall.sql
This SP nicely summarizes the free space, total space, and used space
in the data and log segments for each database in a server. It also
expresses the free space as a percentage.
Usage : sp_freeall (no parameters)
sp_genddl.sql
This procedure will print out the table ddl (and the ddl for indexes on
that table) in cut-and-paste sql format. It has a fault when analyzing
indexes however; it cannot distinguish between table level constraints and
actual indexes. It is useful however to quickly grab the table ddl.
Usage: sp_genddl [table name]
sp_helpcode.sql
Description: Works similar to standard system stored procedure
sp_helptext. Correclty handles cases when a substring
begins in one row of syscomments table and continues
in the next (no split lines!).
Uses print command (not select) to generate the result
for technical reasons.
Usage: sp_helpcode [object] where object is either a Stored Procedure or a Trigger (or another type of object w/ entries in syscomments)
sp_io.sql
This procedure is an enhanced sp_who. It provides uid, physical_io, and
cpu time in addition to normal sp_who fields.
Usage: sp_io (no parameter)
sp_ioa.sql
This is the same stored procedure as sp_io above. sp_ioa limits its output
to procedures that are "active." It determines active by examining the
command being executed by the spid in question. If its equal to "AWAITING
COMMAND" it isn't shown. Can be decieving b/c a job could be active and
temporarily paused the moment you run sp_ioa (or sp_who). A better indicator
of activity is to run sp_io twice and compare the physical_io field for
changes.
Usage: sp_ioa (no parameters)
sp_opentran.sql
sp_opentran quickly obtains information out of sysprocesses for only
open or pending transactions
Usage: sp_opentran (no parameters)
sp_servermap.sql
A GREAT stored procedure that completely maps an entire SQL Server. I
had been looking for this stored procedure for years and just refound
it. You'll have to run it to believe it. Updated 12/12/02. Mark
Kita
Usage: sp_servermap (no parameters) or [ABCDEF] where
sp_show.sql
This is a quick and dirty stored procedure I wrote because I got so sick
of typing in the SQL command to show all user tables or all stored
procedures. It will convert lowercase letters automatically to uppercase,
since thats how object types are stored in sysobjects. Just for review:
Usage: sp_show [letter representing object type]
sp_showfrag.sql
This procedure was created by modifying sp_spaceused so that a
listing for all tables is generated rather than showing information
one table at a time. It seems like its broken (according to what its
supposed to provide) but it does offer some useful information. In
addition to the sp_spaceused information, it analyzes each table in the
database and prints out spaceused information for each table.
Usage: sp_showfrag [table] (though as mentioned above it really doesn't matter what table you pick; you're getting info about all of them. If you pick a non existant table or another type of object, you'll recieve an error message.)
sp_thresholdaction.sql: see the sp_thresholdaction section off the table of contents menu.
sp_vdev.sql
A very nice stored procedure that reports all SQL devices and how
much free space there is for further allocation to databases.
Usage: sp_vdev (no parameters)
sp_whom.sql
This procedure was adapted by Todd Boss from sp_who, and adds two features:
1. replaces the useless command status field with the physical_io field
from sysprocesses, and 2. allows the user to supply an optional parameter
of either username or database, and the sp_whom information will be limited
to processes either owned by that username or accessing that database.
Caution: having too many stored procedures can fill up sybsystemprocs and cause problems. I don't have all these installed in my sites..just the ones i find most useful. This is the collection I typically install