DatabaseJournal.com Staff

This stored procedure was created to allow one to change logical file names of datbase files in a 7.0 database. After doing a restore with the move option, one can change the physical file path and physcal file name, but not the logical name. This posed a problem because some have internal standards for database […]

This script consists of 9 stored procedures intended to make profiling (timing) of pieces of code in stored procedures and triggers a breeze. Automatically adds code to stored procedures and triggers to save elapsed time, CPU time and Physical IO. Stored procedures are provided to initialize and terminate profiling in a database, to setup, restore, […]

sp_drop_constraints will drop all constraints on the specified table, including CHECK, FOREIGN KEY, PRIMARY KEY, UNIQUE, and DEFAULT constraints. Compile it in your master database and use it from any user database. Author: Doug Bass Download Script: usr_sp_drop_constraints.sql Disclaimer: We hope that the information on these script pages is valuable to you. Your use of […]

These stored procedures will work in SQL Server 6.5 and 7.0 and are used to automatically create insert, update, and delete statements based on the tables metadata. Author: Hemant Sahgal Download Script: genscriptsreadme.txt uspsys_GenAdd.sql uspsys_GenDel.sql uspsys_GenMod.sql Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information […]

corrupt65db.exe — this self-extracting file contains a c program, a .MAK file, and an exe for Killit. Killit will corrupt a 6.5 database. There are also scripts to create and populate a database. The README.TXT file describes the steps necessary to use the program. It will not work unless you follow the directions. Note that […]

sp_DBABCPOut.sql Exports user data from a database, and sp_DBABCPIn.sql Imports data into a database. They use the – T ( Trusted connection ) to run the BCP as the work is being done with xp_cmdshell which we limit execute permission to. With the export script, sp_DBABCPOut.sql, pass in the Database name, the server name and […]

Create TimeStamp triggers for all user defined Tables This uses a two pass system. The first pass generates the Triggers that affect the UpdateStamp fields and the second pass creates the triggers that affect the CreateStamp fields. Author: Mike Meerding Download Script: CreateTriggers.sql Disclaimer: We hope that the information on these script pages is valuable […]

This stored procedure accepts the parameters @DbName and @Instance. It finds an instance of @Instance from all the stored procedures in @DbName. It is particularly useful if you want to list all the stored procedures that reference a specific table. This tool is for MS SQL Server 6.5. Author: Gil Tungol Download Script: sp_FindInstanceInSP.SQL Disclaimer: […]

The sp__GenerateInsertUpdateProcedure interrogates the INFORMATION_SCHEMA views for table meta-data. Then based upon that meta-data it generates a stored procedure that can be used to perform an insert/update operation. If a record does not exist based upon the defined primary key, the procedure will insert a record. If a record does already exist based upon the […]

CursorThroughTablesExecuting sp__GenerateInsertUpdateProcedure.sql (CTTEsp_GIUP.sql) is a script that can be run from any user database that will call the sp__GenerateInsertUpdateProcedure for each table with that has a primary key defined and output the necessary code for creating insert/update routines. Simply cut and paste the generated code into a new window to execute it and actually create […]

Procedure to search for a phrase on all database. To apply so: exec spSearchOnAllDB ‘Sugar%’ exec spSearchOnAllDB ‘%soft%’ exec spSearchOnAllDB ‘_5234_57%’, 1 exec spSearchOnAllDB M_cro_oft Author: Gleb Oufimtsev Download Script: srch.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, […]

Dumps all Tablenames, columns, sp_column descriptions of all your tables into one table for queries. Great for checking columns against tables quickly! (Uses sp_columns and cursor) Author: Anthony Loera Download Script: conv_table.SQL Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information contained in these pages, […]