DatabaseJournal.com Staff

Creating a Database Plan Database File Locations Three control files Three groups of redo log files, with each group having two members Create a file structure for the data files Creating a Database Manually Decide on a unique instance name and database character set Set the operating system variables (UNIX) ORACLE_HOME ORACLE_SID ORACLE_BASE ORA_NLS33 PATH […]

Here’s a handy way to send a message to everybody currently logged into a given SQL 7 database. Since it relies on a ‘net send’ to the host name, its usefulness is limited to situations where the client machines’ host names are known to SQL Server and are reachable by a ‘net send’ command. Author: […]

This procedure accepts an integer parameter (as # of days) and will show all active SQL connections older than the number of days specified. The script has been tested in SQL Server 2000. Example: exec sp_connections ‘3’ Author: Lauryn Bradley Download Script: sp_connections.txt Disclaimer: We hope that the information on these script pages is valuable […]

This is a simple stored procedure that can be used to drop tables. It will accept a table name as input and drop all constraints on the table before executing a DROP TABLE command. Author: Jason Bohanon Download Script: sp_drop_table.sql Disclaimer: We hope that the information on these script pages is valuable to you. Your […]

This script will truncate all tables in a database using INFORMATION_SCHEMA view. The script works for versions of SQL Server with the view, namely 7.0 and above. Author: Richard Fennimore Download Script: TruncTables.sql Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information contained in these […]

This stored procedure will perform RC4 encryption/decryption on strings up to 255 characters in length. The procedure works as is on SQL 7 and 2000; in order to run on SQL 6.5, you will need to change the SET statements to SELECT statements. Author: Brad Baker Download Script: RC4.sql Disclaimer: We hope that the information […]

This script is designed to undo committed transactions, functioning as an “UNDO” service in multiuser mode. The script works only in SQL Server 2000. The following is a description of how the script works — for more information see the readme file included in the script download: “When you do changes in the database, SQL-UNDO […]

Purpose The purpose of this article is to help you become familiar with one of Oracle9i’s new features, the server parameter file–aka SPFILE. Audience The primary audience of this article is DBAs working on previous versions of Oracle prior to 9i and interested in using this new feature. Some test cases have also been explored […]

Here’s a simple script that checks to see if a job is currently running. Updated on 1/18/02 with a working example of how to use the script in the comments section. Author: Allan Saywitz Download Script: sp_IsJobRunning.sql Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the […]

Here’s a simple query that will generate CREATE TABLE scripts for the connected user. Author: Arul Kumar Download Script: Generate_Tables_Script.sql Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is […]

Here are three general purpose scripts for degragmenting and speeding up large databases. You can schedule the SPs for periodic execution, but they should be run in absence of user activity on the database (might slow down users otherwise). The SPs were tested against SQL Server 7.0 but should also work on SQL Server 6.5 […]

This is an add-on to the script that was posted earlier by Senthil Kumar on 11/16/01. It will generate a script to display the name and the structure of each table in a database. The script was created in and tested on SQL Server 7. Author: Mohamed Hamdy Mohyeldin Download Script: metadata_for_tables.sql Disclaimer: We hope […]