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]
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]
This procedures identifies all spid's that are blocking other spid's, but are not themselves blocked.
Usage : sp_blocker (no parameters)
This procedure is a nicely formatted sp_help
Usage : sp_describe [table-name]
This procedure calculates neatly the total device space in use for all database devices.
Usage : sp_devspace (no parameters)
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)
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)
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]
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)
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)
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 quickly obtains information out of sysprocesses for only open or pending transactions
Usage: sp_opentran (no parameters)
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
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]
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.
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)
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