Beginning Transact-SQL with SQL Server 2000 and 2005

12

Creating and Managing Database Objects

My friend Eric said that I should have titled this chapter “Going Fishing.” I commute and do much of my writing on the Washington State ferries and I get a lot of input from my fellow commuters. When I asked Eric “Why Going Fishing,” he said that managing database objects is boring and that people would be more interested in fishing stories than creating tables. The fact is that creating and managing database objects is pretty simple (and, yes, mundane) stuff. However, there is little doubt that this is an essential part of the process. After all, how can you use a database until it's been designed and created?

Anything you can do with SQL Server's graphical or console management tools can be done with script. Many of the menu options in Enterprise Manager and Management studio actually use the Transact-SQL script you'll be using in this chapter.

Data Definition Language

You can do only three things with any database object, other than actually use it; you can create it, alter it, or drop it to get rid of it. Every type of database object at least supports the CREATE and DROP statements. Of course, there is actually a little more to this story. Depending on the object type, there are a number of options affecting certain capabilities and settings. You've already seen a number of these statements, because it would have been difficult to cover earlier topics without first showing you how to create some objects.

Unless permissions are explicitly ...