DatabaseJournal.com Staff

Here’s a package of three useful administrative scripts, one of which is the “Terminate All User Processes” listed below. Here’s a brief description of the other two scripts: uspSetSingleUser (as follows from its name) sets a database in single user /dbo only mode. It utilizes uspKillAllProcesses and can be used for restore database automation. uspShrinkDbLog7 […]

This script will attempt to kill all processes in a given database belonging to a given user. If the user is not specified it will attempt to terminate all users’ processes in the database. If the database name is not specified it will hunt the processes in all databases where such a user name exists. […]

Above Software has made available a free beta release of their new Statement Tracer for Oracle software. Statement Tracer can be downloaded and used for free through the end of April (an official release is expected in early May). Here’s a link to the download site for the 1.0.1.5 Beta Release: http://www.aboves.com/downloads. Here’s a short […]

This SQL 2000 user-defined function performs RC4 encryption/decryption on strings up to 255 characters in length. Based on Brad Baker’s RC4.sql script, it runs about 100 times faster, fixes a minor bug, and is packaged as a UDF. It can be modified for SQL7 by putting in a stored procedure. For SQL Server 6.5, the […]

This utility procedure will rename SQL Server to a given name, by default the WINS machine name. The script works on SQL Server 7.x and 2000. Notes: 1. You will need to restart the SQL Server service in order for the new name to take effect. 2. Be careful using this procedure with named instances […]

Written for SQL Server 2000, this script is useful for finding missing constraints between two databases that are supposed to be identical. The Script has the ability to show all constraints per database, as well as the ability to script out missing constraints to make addition easy. Author: Luke Malyurek Download Script: LM_FindConstraintDiff.sql Disclaimer: We […]

This stored procedure is a refinement of work previously done by Sumit Dhingra. It takes a string argument of fields delimited by the 3rd argument character, or a comma if the 3rd argument is missing, and applies each field to the second argument which may be either a SQL statement or, more usually, a stored […]

Here are two scripts designed for backup purposes — the first returns the last date a database was backed up and the second reports on backup activity for all databases, sorted by date of backup. The Get Last Backup script allows you to report the last date of backup for all databases on a server, […]

This stored procedure will script all indexes, primary keys and foreign keys. It’s useful as a documentation tool and also for being able to generate scripts that can be used while making modifications that require referential integrity to be removed. A full explanation of the switches required to generate the various output options are included […]

This stored procedure is designed to view database information in an active database — i.e. database, tables, objects, users, jobs, etc. The script basically acts as a ‘wrap-around’ and enables one procedure to be used to view a wealth of database information. Tested on SQL Server 2000. Accepts viewtype parameters of ‘database’, ‘tables’, ‘objects’, ‘jobs’, […]

This script keeps track of blocked processes, blocking processes, blocked statements, blocking statements and wait time, etc. It will be most useful when run every five seconds. The script creates a stored procedure usp_checkblocking in the master database and creates two temporary tables for gathering the DBCC inputbuffer output. Author: Venkata Kumar Download Script: block.sql […]

This script is convenient for anyone who needs to move a lot of SQL server data or log files around for any reason. It dynamically generates the scripts needed to detach and attach all your databases. It can be edited to change file locations on the attach statements if needed. The script has been tested […]