Professional SQL Server™ 2005 Programming

4.3. The ALTER Statement

Okay, so now you have a table — isn't life grand? If only things always stayed the same, but they don't. Sometimes (actually, far more often than you would like), you get requests to change a table rather than recreate it. Likewise, you have needs to change the size, file locations, or some other feature of your database. That's where your ALTER statement comes in.

Much like the CREATE statement, your ALTER statement pretty much always starts out the same:

ALTER <object type> <object name>

This is totally boring so far, but it won't stay that way. You'll see the beginnings of issues with thisstatement right away, and things will get really interesting (read: convoluted and confusing!) when you deal with this even further in the next chapter (when you deal with constraints).

4.3.1. ALTER DATABASE

You can get right into it by taking a look at changing your database. You'll actually make a couple of changes just so you can see the effects of different things and how their syntax can vary.

Perhaps the biggest trick with the ALTER statement is to remember what you already have. With that in mind, take a look again at what you already have:

EXEC sp_helpdb Accounting

So, the results should be just like they were when you created the database:

name	db_size	owner	dbid	created	status	compatibility_level
Accounting	15.00 MB	sa	9	May 28 2005	Status=ONLINE, Updateability= READ_WRITE, UserAccess= MULTI_USER, Recovery=FULL, Version=598, Collation=SQL_ Latin1_General_ CP1_CI_AS, ...