How to: Delete Database Objects

How to: Delete Database Objects

To delete a database object from a database project, you can delete the object from Schema View, or you can delete the file that contains the object definition from Solution Explorer. You must follow a different process to delete table columns and the parameters of a stored procedure or function. After you delete one or more objects from a database project, you must build and deploy the changes to your database server.

Deleting Database Objects

To delete an object from a database project

1. 
   If Schema View does not appear, open the View menu and click Schema View.
   
2. 
   In Schema View, click the object that you want to delete.
   
3. 
   On the Edit menu, click Delete. You can also click the object and press the DELETE key.
   
   A confirmation dialog box appears.
   
4. 
   Click OK in the confirmation dialog box.
   
   The object is deleted from the database project in both Solution Explorer and Schema View. If the database project is under version control, when you delete the object, the project file will be checked out. Deleting an object from Schema View also deletes all child objects. For example, if you delete a table, all keys, triggers, indexes, constraints, and statistics are also deleted.
   
   For the change to be reflected on the database server, you must build and deploy your changes.
   

To delete the file that contains an object definition from a database project

1. 
   In Solution Explorer, click the file that represents the object that you want to delete.
   
2. 
   On the Edit menu, click Delete. You can also click the object and press the DELETE key.
   
   A confirmation dialog box appears.
   
3. 
   Click OK in the confirmation dialog box.
   
   The object is deleted from the database project in both Solution Explorer and Schema View. If the database project is under version control, when you delete the object, the project file will be checked out. If the object you deleted had child objects, such as indexes or keys on a table, the child objects will appear in the Orphaned Objects folder in Schema View. Errors will appear in the Error List indicating that the parent object cannot be found.
   
   For the change to be reflected on the database server, you must build and deploy your changes.
   

MySQL Empty Database / Delete or Drop All Tables

MySQL drop all tables syntax:

DROP DATABASE {mysql-database-name}

Method #1: Empty database with root user

In order to use this procedure you must have the drop and create database privilege (otherwise you will drop database but not able to create it again). Login as MySQL root or admin user to drop atomstore database:

$ mysql -u root -p

Now drop database:

mysql> DROP DATABASE atomstore;

Now create database again:

mysql> CREATE DATABASE atomstore;

Exit and close the session:

mysql> quit

Method #2: Drop all tables using shell script w/o root access

I've small handy shell script that removes all tables without dropping and creating MySQL database again.

#!/bin/bash
MUSER="$1"
MPASS="$2"
MDB="$3"
 
# Detect paths
MYSQL=$(which mysql)
AWK=$(which awk)
GREP=$(which grep)
 
if [ $# -ne 3 ]
then
 echo "Usage: $0 {MySQL-User-Name} {MySQL-User-Password} {MySQL-Database-Name}"
 echo "Drops all tables from a MySQL"
 exit 1
fi
 
TABLES=$($MYSQL -u $MUSER -p$MPASS $MDB -e 'show tables' | $AWK '{ print $1}' | $GREP -v '^Tables' )
 
for t in $TABLES
do
 echo "Deleting $t table from $MDB database..."
 $MYSQL -u $MUSER -p$MPASS $MDB -e "drop table $t"
done

S