Tables Maintenance


The List of Tables of a Database

While creating the tables of a database, it is important to keep an account of the already existing tables of a table. The SQL allows you  to get a list of tables that belong to a database. To display this list in MySQL, use the SHOW TABLES command:


In MSDE or Microsoft SQL Server, to see the list of tables of the current database, execute the sp_tables statement (it is a stored procedure).


Practical Learning Practical Learning: Viewing the List of Tables

  • To view the list of the existing tables of the current database, execute the following statement:
    SHOW Tables;

Renaming a Table

If you find out that the name of an existing table doesn't fit your need, you can change it. To rename a table in MySQL, use the following formula:

RENAME TABLE ExistingTableName TO NewName;

To rename a table in the MSDE, execute sp_rename (it is a stored procedure) using the following formula:

EXEC sp_rename ExistingTableName, NewName;


Deleting Tables

If you create a table by mistake or for any other reason you don't need a particular table in a database, you can remove it. To delete a table, you use the DROP TABLE expression followed by the name of the table. The syntax to use is:


As always there are two issues to keep in mind. First, the table must exist in the current database. If it doesn't, you will receive an error. Second, and very important, when you delete a table with the DROP TABLE command, you will not receive a warning.

Practical Learning Practical Learning: Deleting Tables

  1. Execute the following statement:
    DROP TABLE Employees;
  2. Notice that you didn't receive a warning but the table has been deleted
  3. In the same way, execute the following statement:
    DROP TABLE IDCardHolders
  4. Execute the following statement:
    DROP TABLE Drivers;
  5. Execute the following statement:
    USE Exercises;
  6. Execute the following statement:
  7. Type Exit and press Enter to leave the command prompt

Previous Copyright © 2004-2010 FunctionX, Inc. Next