Columns Maintenance

Column maintenance consists of reviewing or changing any of its aspects. This includes reviewing the structure of columns of a table, renaming a column, deleting a column, changing the data type or the nullity of a column, etc.

When making a change on a column, you are also said to alter the table. To support this operation, SQL starts with the following formula:

ALTER TABLE TableName

When using this statement, the ALTER TABLE expression is required and it is followed by the name of the table.

To see the structure of a table in the Enterprise Manager, in the right frame, you can double-click it. This would open the Tables Properties dialog box:

To view the columns of a table using SQL code, in a Query window of the SQL Query Analyzer, execute sp_columns followed by the name of the table they belong to. Here is an example:

This action displays the list of columns in the COLUMN_NAME column and other characteristics on the right columns.

Adding a New Column

After a table has already been created, you can still add a new column to it.

To add a new column in SQL Server Enterprise Manager, first right-click the table and click Design Table. To add a new column to the end of the table, click the first empty field under Column Name, type a name, and specify the other options.

To insert a new column between two existing one, right-click the column that will succeed it and click Insert Column. This would create a new empty field. Type the desired name and specify the other options.

In SQL, the basic formula to add a new column to an existing table is:

ALTER TABLE TableName
ADD ColumnName Properties

The ColumnName factor is required. In fact, on the right side of the ADD keyword, define the column by its name and using all the options we reviewed for columns.

Here is an example:

ALTER TABLE StaffMembers
ADD Address varchar(100) NULL
GO

When this code is executed, a new column name Address, of type varchar, with a limit of 100 characters, and that allow empty entry, will be added to a table named StaffMembers in the current database.

Renaming a Column

If you find out that the name of a column is not appropriate, you can change it. To rename a column in the Enterprise Manager, right-click the table that the column belongs to and click Design Table. In the Design Table, highlight the name of the desired column to put it into edit mode and edit it.

In SQL, to change the name of a column, in a Query window of SQL Query Analyzer, execute sp_rename using the following formula:

sp_rename 'TableName.ColumnName', 'NewColumnName', 'COLUMN'

The sp_rename factor and the 'COLUMN' string are required. The TableName factor is the name of the table that the column belongs to. The ColumnName is the current name of the column. The NewColumnName is the desired name you want to give to the column.

Here is an example:

sp_rename 'StaffMembers.FullName', 'EmployeeName', 'COLUMN'
GO

When this code is executed, the interpreter will look for a column named FullName in the StaffMembers table of the current or selected database. If it finds that column in the table, then it renames it EmployeeName.

Deleting a Column

If you have an undesired column that you don't want anymore in a table, you can remove it. To delete a column in the Enterprise Manager, right-click the table that the column belongs to and click Design Table. In the Design Table, right-click any field of the undesired column such as its name or its data type and click Delete Column. You will not be warned before the column gets formally deleted.

To delete a column, in a Query window of SQL Query Analyzer, use the following formula:

ALTER TABLE TableName
DROP COLUMN ColumnName

On the right side of the ALTER TABLE expression, type the name of the table. On the right side of the DROP COLUMN expression, enter the name of the undesired column. Here is an example:

ALTER TABLE StaffMembers
DROP COLUMN CurrentResidence
GO

When this code is executed, the interpreter will look for a column named CurrentResidence in a table StaffMembers of the current or selected database. If it finds that column, it will remove it from the table.

Practical Learning: Setting Fields Properties

1. Return to the SQL Server Enterprise Manager and expand the Tables node of the BCR database
2. Click the Tables node to select it.
   In the right frame, right-click Create a new database named LiquorStore
3. Create a new table and add the following fields to it EmployeeNo and DataHired
4. Save the table as Employees
5. In the top section of the table, click EmployeeNo to select it and press F6.
6. In the lower section of the table, in the Description field, type
   Employee Number set by the Management
7. Complete the table as follows:
    
   
8. In the top section, click DateHired and press F6. In the Description field, type:
    
   Date the employee was formally hired
9. In the top section, click Country
10. In the lower section for the Country field, click the Default Value field and type USA
11. In the upper section, click MaritalStatus
12. In the lower section for the MaritalStatus field, set the Default Value to 0
13. After using the table, to close it, click its Close button
14. When asked whether you want to save the changes, click Yes