DatabaseJournal.com Staff

sp_hywho is an enhanced version of sp_who that shows more detail process information and also checks for and displays blocking information. It will also accept a database as a parameter to view information for only a specific database. It only works on SQL 6.5 and 7.0. Author: David Satz Download Script: sp_hywho.sql Disclaimer: We hope […]

This stored procedure is a modified version of the system procedure sp_help which presents an easy to read meta-data listing for each or selected tables in the current database. Index and constraint information is also provided. Enter “sp_tableinfo” with no arguments or “sp_tableinfo ‘?’” to get additional documentation for the procedure. Note also that this […]

Here’s a useful stored procedure that returns random records. The download includes the complete definition and examples. Author: Mauro C. Pichiliani Download Script: random.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 […]

I have written this script which, although simplistic, is a good tool for other tech support people to send to clients to obtain a snapshot of their database and server. Author: Vito D’Astici Download Script: 7.0_ServerInfo.SQL Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information […]

I have written this script which, although simplistic, is a good tool for other tech support people to send to clients to obtain a snapshot of their database and server. Author: Vito Dastici Download Script: 6.5_ServerInfo.SQL Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information […]

This stored procedure (SQL v7) / user function (SQL 2000) allows you to convert hex strings into integer numbers. Examples: SQL Server 2000. PRINT dbo.HexToInt(‘0x7ffff’) SQL Server v7.x DECLARE @intNumber INTEGER EXECUTE @intNumber = HexToInt OUTPUT,’0x7ffff’ PRINT @intNumber Author: Paul Hobbs Download Script: HexToInt.sql Disclaimer: We hope that the information on these script pages is […]

Did you ever wonder how to find the difference between two dates in the form of X days, Y hours, and Z minutes? Since SQL Server does not provide any built-in functions for this, we’ve designed the script below to create the user-defined function named DiffBetweenDates (only supported on SQL Server 2000). This function takes […]

This script compares the tables and columns of one database to the same items in another database. It works on SQL 2000 and SQL v7.x databases, and on 6.5 databases with some restrictions, and can also be turned into a stored procedure with minimal effort. Updated on May 21st with a fix for a missing […]

This stored procedure allows you to search all store procedures within a database for any reference to a specified string of text. Example: EXECUTE findText ‘lineno’ ————————————————————————————————————— Procedure Line Reference sea 39 DECLARE @lineNo INTEGER sea 74 SET @lineNo = 0 sea 82 SET @lineNo = @lineNo + 1 sea 85 PRINT @proc + CHAR(9) […]

This procedue creates a Job to backup a given database to the specified location, however you need to schedule this job (see next script) or run it manually in order to actually backup the database. Input Parameters: @DatabaseName, @BackupLocation, @JobName; the script has also been modified to include the filename as an optional input parameter. […]

This procedue schedules a specified job to either run daily or weekly on a specified day. Input Parameters: @DatabaseName, @BackupLocation, @JobName; the script has also been modified to include the filename as an optional input parameter. (Supports SQL 7.0, 2000, and even MSDE) Author: Vijaykumar Aski Download Script: usp_ScheduleMyJob.sql Disclaimer: We hope that the information […]

This script will create an insert statement with values for each row in the passed table. It can handle translation of Null values and all data type translation except blob types Uses the primary key to generate either a if exists delete or if not exist before the insert statement If table has an Identity […]