DatabaseJournal.com Staff

This script finds columns in a database with similar names. When designing a large database it is sometimes handy to be able to find all occurances of, say, “material_key”. This script has a string argument for all or part of a field name: e.g. sp_findcol material. It will return all fields in the database with […]

Here are five general purpose procedures I wrote for SQL Server 6.0 to reverse engineer and drop foreign key constraints. The procs are handy for quickly generating a list of all the constraints for a table or for dropping constraints so that you can modify a table. They can be used in conjunction with other […]

If you are implementing a database and find that you have duplicate keys, you cannot create a primary key until you eliminate the duplicates. If you have a lot of patience, see Knowledgebase article Q139444. If you just want to clean out the duplicates, then run this script. It takes a table name as an […]

While cleaning up data you may create orphan records (before you have created FK constraints). This script purges orphan records from a child table. It takes the arguments of parent_table, child_table and linking_field and then finds all the child records with no parent record and deletes them. For SQL Server 6.5 Author: Tom De Lancey […]

Every site should have two “auto-log-dump” tasks for each database. First, should be an alert set on the 1105 error: syslogs is full. It should do a dump tran with no_truncate. The second is an auto dump that tests to see if a log is over X percent full and if so, DUMP TRAN. The […]

This proc will remove a database an all the devices that this database exclusively uses. For SQL Server 6.5 Author: Tibor Karaszi Download Script: sp_removedevices.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 […]

This script will take an entire server and migrate the data to another server. It walks down database by database, table by table. It requires that the schemas match on the two servers. (A future enhancement will detect any differences.) It has also been streamlined with much of the hard coding taken out. There are […]

This procedure will attempt to fix the broken links caused by a DUMP on one system and a LOAD on another. It updates the suid in the restored database to match the names on syslogins of the restored server. This procedure will NOT fix aliases, since these depend on the suid of the source system’s […]

This procedure will attempt to fix the broken links caused by a DUMP on one system and a LOAD on another. It updates the suid in the restored database to match the names on syslogins of the restored server. NOTE: This procedure will NOT fix aliases, since these depend on the suid of the source […]

This WORD document is a form to help request and document changes to databases, tables and other objects in a production environment. For SQL Server 6.5 Author: Jeff Boyd Download Script: DBMOD.DOC Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information contained in these pages, […]

This is a modification of the sp_spaceused procedure. I put it in a weekly scheduled task so I can monitor database growth. You can check the task history window to see the results, or, you can specify an optional LOG parameter and it will posts the results to the NT Application log. I run it […]

I had five databases with thousands of tables each. Some of these tables are large (aprox: 500 MB 1500 MB). I needed to run update statistics daily because my small tables suffer heavy changes (inserts / updates and deletes). Everyday new tables were created and old ones dropped. I’ve created a control table to store […]