DatabaseJournal.com Staff

Compare a source and target database. List all procedures, tables, views, and triggers on the source that either don’t exist on the target, or are later on the source than the target machine. This is useful when a series of changed objects need to be installed from a development server to a production server. For […]

Pause the server before kicking users off Pause the server before shut down … The stored procedure default to 5 seconds pause, no one would be able to connect, and then it resumes. For SQL Server 6.5 Author: William Zhou Download Script: sp_pause.sql Disclaimer: We hope that the information on these script pages is valuable […]

Put sp_spacehistory in master database. Put sp_spacemon in database where your recorded table is. Schedule a task to run sp_spacemon. A table (spacehistory) will be created and all the table space usage information will be recorded against each user defined databases. master, tempdb, model, pubs, msdb will not be recorded. sp_spacehistory is a cutdown version […]

This procedure displays the same as sp_spaceused does, but for all user tables in the current database. In addition I indicate what types of triggers exist on each table For SQL Server 6.5 Author: Chuck Lucking Download Script: sp_allspace.sql Disclaimer: We hope that the information on these script pages is valuable to you. Your use […]

If you have a blocking problem (a process in the middle of a transaction that has resources locked and other processes are in a live lock, or deadlock waiting for these resources to become available). This is a quick way to display what processes are causing the block(s) and what processes are being affected by […]

The main purpose of this procedure is to display the activity of a given spid. It shows 1) A summary from sysprocesses including an English description of waittype and how long the spid has been active. 2) The input buffer 3) Current transaction count 4) An English description of any pstat status codes 5) The […]

Produces a list of six reports. A – DATABASE SEGMENT MAP B – DATABASE INFORMATION C – DEVICE ALLOCATION MAP D – DEVICE NUMBER, DEFAULT & SPACE USAGE E – DEVICE LOCATION F – MIRRORED DEVICES For SQL Server 6.5 Author: Alan Enderby Download Script: sp_servermap.sql Disclaimer: We hope that the information on these script […]

Fed up with all those converts, + signs and quote problems when formatting an error/information message? Format_string allows you to construct a message with place holders that get substituted by supplied variables. For SQL Server 6.5 Author: Alan Enderby Download Script: replace_script.SQL Disclaimer: We hope that the information on these script pages is valuable to […]

This SP will dump all databases except pubs and tempdb to a tapedevice, regardless of how the tapedevice is named; the SP determines the name of the device. It also checks for the existence of a tapedevice and checks if multiple tapedevices are attached. The tape will be erased when dumping the first database, but […]

Table maintenance script with email of for key results – includes logging. NOTE: you must make modifications to the scripts in order to have them point to the right mail accounts. For SQL Server 6.5 Author: Mickael R. Escroignard Download Script: sp_Table_Maintenance_II.SQL Disclaimer: We hope that the information on these script pages is valuable to […]

This script will search & replace text in (var)charfields. I wrote this sp, because I have to maintain a lot of European data. Due to a mix of different languages (especially the ‘goofy’ German and French characters), citynames etc were spelled in several ways. To formalize this, you’ll need this sp. The format is: usp_search_replace […]

Here is a script that will retreive the next available device number. It’s ery useful if creating a device the old fashioned way. For SQL Server 6.5 Author: Mike Schellenberger Download Script: sp_ms_nextdevicenumber.sql Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information contained in these […]