Three Small Admin Scripts
Here’s a package of three small administrative scripts for adding fields, dropping fields, and checking to see if a specific field exists in a table. Here’s a brief description for each of the scripts:
spGetFieldsExists takes a tablename and a field name, and checks if that field exists in the table. It simply returns 0 or 1 (false or true).
spAddField is the procedure I use to add all fields to the db in our upgrade scripts. With this proc, you pass it the tablename, fieldname, datatype, and null or not null. This will call spGetFieldsExists and see if the fields exists or not. If it does, spAddField will drop that column. Then it readds it. This allows me to execute a field add script over and over without error because the field exists. It drops the field, assuming that the new version is different. Side effects of this is that the position of the field will change, if it exists already. But usually it’s a new field, making it OK to run over and over, since it’s adding it at the end anyway. Customize as needed.
spDropField does exactly the opposite of spAddField. It checks for existence, then drops it if it does exist.
Author: TJay Belt
Download Script:
spAddDropField.txt
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 provided
“as -is”, without any warranty, whether express or implied, of its accuracy,
completeness, or fitness for a particular purpose…
Disclaimer Continued
Back to Database Journal Home