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.

One of the routine operations you can perform on an existing table before taking any other action consists of reviewing its structure. For example, you may want to see the names of columns, their data types, and other related information that we haven't reviewed yet.

In MySQL, to view the structure of a table, you can use the following expression:

DESCRIBE TableName;

As always, the TableName must exist in the current database, otherwise you would receive an error.

In MSDE or Microsoft SQL Server, to see the structure of a table, execute the sp_help statement (it is a stored procedure) while entering the name of the table to its right. An example is:

sp_help Departments

In MSDE rather than Microsoft SQL Server, the result is not cute.

Practical Learning: Reviewing the Tables of  a Database

• At the prompt, if you are using MySQL, execute the following statement:
   
  DESCRIBE EmploymentAgency;
   

Adding a New Column

After a table has already been created, you can still add a new column to it. 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.

Practical Learning: Adding a New Column

Renaming a Column

If you find out that the name of a column is not appropriate, you can change it. In MSDE, to change the name of a column, 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, 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.

Practical Learning: Deleting a Column

1. To remove a column from the Contractors table, execute the following statement:
    

   ALTER TABLE Contractors DROP COLUMN MaritalStatus;

2. To review the current structure of the Contractors table, execute the following statement:
    

   DESCRIBE Contractors

3. To change the database, execute the following statement:
    

   USE Exercises;

4. To delete the EmploymentAgency database, execute the following statement:
    

   DROP DATABASE EmploymentAgency;

5. To end the lesson, type exit and press Enter