Creating Tables Using Table Designer

Enterprise Manager provides a handy tool, the Table Designer, to allow you to easily create tables from the GUI. To invoke Table Designer, expand the database, right-click Tables, and select New Table. This invokes the Table Designer as shown in Figure 12.2.

Figure 12.2. Using Table Designer to create a table.

After you have entered your column names and definitions, click on the Save (disk) icon and you will be prompted to name the table, as shown in Figure 12.3.

Figure 12.3. Naming the table in Table Designer.

The Properties page of Table Designer allows you to create and manage constraints and indexes, as well as define ownership and filegroup placement for the table. The Properties page is shown in Figure 12.4.

Figure 12.4. The Table Designer Properties page.

When you have finished defining your table, click OK, and Table Designer will generate the required Transact-SQL and create the table.

Adding/Removing/Modifying Columns Using Table Designer

After a table has been created, it is easily modified using Table Designer without having to write ALTER TABLE statements. In Enterprise Manager, simply right-click the table you want to edit and select Design Table. Of course, when editing a table's properties in this manner, all the restrictions pertaining to the ALTER TABLE command apply.

Generating Scripts to Create Tables

At this point, I suspect those of you who are new to database administration are asking yourselves why you would ever want to get into all that CREATE TABLE / ALTER TABLE stuff when you can use the Table Designer. On the other hand, many experienced administrators probably skipped this section because they know that the value of using scripts over GUIs to create objects is that the scripts can be saved in case the object needs to be re-created. Microsoft addressed this by including a Save Change Script icon on the Table Designer toolbar. When you have created or modified a table, click this icon (the third icon from the left) to generate the script required to re-create your changes. You also can generate complete scripts at any time for your tables and other objects from the Tools menu's Generate SQL Script item. If you use Table Designer to create or modify tables, do yourself a favor and always save your changes to a script. Figure 12.5 illustrates using Table Designer to generate a script for the addition of a Primary Key constraint.

Figure 12.5. Using Table Designer to generate a script.