Clean up the environment so that all data integrity, view, and stored procedures will work

DELETE Statements

5. Clean up the environment so that all data integrity, view, and stored procedures will work

with the new table.

Now let’s take a closer look at each step.

Backing Up Data

Before running any structure modification routines, your first step should always be to back up your data to protect yourself from human or hardware error. There is a single script, table_change.sql, on the CD-ROM to put all the SQL into a single file. For clarity, I’ll break that file up into sections in the Practical Guide.

Renaming Objects To Be Modified

The next step is to rename the existing table so that we may use the current name again within the database. Duplicate names within a database are not allowed. We accomplish this with the sp_rename stored procedure found in the master database. See Listing 5.20 for the syntax of sp_rename and its use in the table_change.sql script.

Note that this procedure, new to Version 6.5, allows you to rename a column. This is helpful in applying a good naming convention to existing data structures on your server. Be sure that all column references are changed to look for the new name as well, or you will be asking for broken code.

Listing 5.20 Sp_rename from the master database.

Syntax

EXEC sp_rename oldname , newname [ , COLUMN | INDEX | OBJECT | USERDATATYPE]

Script

/* Change the name of the existing table */ IF NOT EXISTS (SELECT *

FROM sysobjects

WHERE id = OBJECT_ID('dbo.authorsbak') AND sysstat & 0xf = 3)

BEGIN

/* Sysstat is the object type in the table */ EXEC sp_rename 'authors', 'authorsbak'

END ELSE BEGIN

/* Script will error due to existing backup */

PRINT 'Sorry, cannot run this script with out removing authorsbak.' RETURN

END

To avoid unintentionally overwriting data, it’s a good idea to check if the backup table already exists in case there is data sitting out there already. If the table already exists, I simply return out of the script with a message.

Previous Table of Contents Next

Brief Full Advanced

Search Search Tips

To access the contents, click the chapter and section titles. Microsoft SQL Server Black Book

(Publisher: The Coriolis Group)

Author(s): Patrick Dalton ISBN: 1576101495 Publication Date: 07/01/97

Search this book:

Previous Table of Contents Next

Scripting Objects

The next step is to re-create the table with the new name. I have already placed the code into the script file for you. See Listing 5.21 for this section of code. If you have not created the scripts for each of your objects already, you can generate them again. Refer to Chapter 3 for details on how to generate an object’s scripts through the Enterprise Manager.

Listing 5.21 Object re-creation section of script.

/* Create the new structure */ /* Drop authors if it already exists */ /* This will drop indexes as well */ IF EXISTS (SELECT *

FROM sysobjects

WHERE id = OBJECT_ID('dbo.authors') AND sysstat & 0xf = 3)

BEGIN

/* Sysstat is the object type in the table */ DROP TABLE dbo.authors

END GO

/* Re-create authors with new columns */ CREATE TABLE dbo.authors (

au_id id NOT NULL ,

http://www.itknowledge.com/reference/standard/1576101495/ch05/182-186.html (1 of 4) [1/27/2000 6:16:28 PM]

Go!

Keyword

---

au_lname varchar (40) NOT NULL , au_fname varchar (20) NOT NULL , phone char (12) NOT NULL , address varchar (40) NULL , city varchar (20) NULL , state char (2) NULL , zip char (10) NULL , contract INTEGER NOT NULL )

Again, always check for the existence of an object before dropping it to keep errors from popping up. In the Create Table section, I have specified the structure changes for the new table. The zip column now has a width of 10 characters, and the

contract column is now an integer. We could have used a TINYINT data type here, but to keep with the example we’ll use an integer. The GO statement at the end of this batch will create the new object so that it exists before moving on in the script to the next batch.

Converting And Inserting Old Data

Now that the new schema structure exists, the next step is to get the old data into the new structure. You cannot reference an object before it has actually been created, so the previous GO statement will ensure that the INSERT/SELECT statement shown in Listing 5.22 will not generate an error. The INSERT/SELECT statement actually performs the task of converting the data and inserting the resulting changes into the new structure.

Listing 5.22 INSERT/SELECT section of script.

/* Get the old data from the bak table for new one */ INSERT INTO authors

SELECT au_id, au_lname, au_fname, phone, address, city, state, zip + '-0000', CONVERT(INTEGER,contract) FROM authorsbak

In this statement, I have padded existing data to have four zeros at the end. This was not really needed, but it will help with the look of the old data when compared to the new data on reports or when passed back to a client application. We’ll use the CONVERT() function to change the existing bit data type to an integer before inserting it into the new table. We’ll use a single statement here to perform a block move of the records to the new table. We could also have used a cursor and moved and inserted each record one at a time. The cursor method would still get the job done, but it would require more code and error-checking and take longer to run.

Cleaning Up The Environment

Now we must clean up the environment to make sure all data integrity, views, and stored procedures will work with the new table. The renaming stored procedure changed the references for the constraints bound to this table so they pointed to the new backup table. We now need to add the lines of code to clean up and restore those items. See Listing 5.23 for the cleanup SQL code from the script file.

Listing 5.23 Clean up code from script file.

/* Re-apply the indexes to the new data */ CREATE INDEX aunmind ON authors(au_lname, au_fname) GO

/* Check the data */ SELECT *

FROM authors GO

/* Handle the existing constraints */ ALTER TABLE titleauthor

DROP

CONSTRAINT FK__titleauth__au_id__1312E04B GO

/* Drop the backup table later after verified */ DROP TABLE authorsbak

/* Add new constraints */ ALTER TABLE authors

ADD

CONSTRAINT UPKCL_auidind PRIMARY KEY CLUSTERED ( au_id ) ALTER TABLE titleauthor

ADD

CONSTRAINT FK__titleauth__au_id__1312E04B FOREIGN KEY ( au_id ) REFERENCES authors ( au_id )

A Word On Constraints

I have not covered constraints or views in this book yet, so some of the terms here may a bit confusing. We’ll cover them in detail later in the book, but I need to mention them briefly here because of the nature of their function. Views will not be dropped when you drop an underlying table, so if they exist, they must be dropped and re-created in the cleanup section of this script. The Authors table does not have a view defined, so it does not have any code in the script. You would have to add the code to drop and re-create the view yourself.

Constraints, on the other hand, will not let you drop an object or create a new constraint if the existing name already exists in the database. So we must drop the

constraints that point to this new structure before continuing on in the code and dropping the backup object. For clarity, return the new data to the client so that the modifications can be tested. You have modified the column in an existing structure without losing any data or functionality in your system. Should you want to restore the old structure, that script file appears on the CD-ROM as well; the

table_restore.sql file will return the Authors table to its original state. And now, once again, that helpful reminder: Back up the master database!

Previous Table of Contents Next

Brief Full Advanced

Search Search Tips

To access the contents, click the chapter and section titles. Microsoft SQL Server Black Book

(Publisher: The Coriolis Group)

Author(s): Patrick Dalton ISBN: 1576101495 Publication Date: 07/01/97

Search this book:

Previous Table of Contents Next

Chapter 6

In document Microsoft SQL Server Black Book (Page 171-177)