How do I delete all tables in a database using SqlCommand?

Question

Unlike the other posts about the task "delete all tables", this question is specifically about using SqlCommand to access the database.

A coworker is facing the problem that no matter how it attempts it, he can't delete all tables from a database via SqlCommand. He states that he can't perform such action as long as there is an active connection - the connection by the SqlCommand itself.

I believe this should be possible and easy, but I don't have much of a clue about databases so I came here to ask. It would be awesome if you could provide a short code example in C# (or any .NET language, for that matter).

If you require additional information, just leave a comment.

Answer 1

You either need to issue a DROP DATABASE command - connect to the "master" database on the server in question, and then issue a

DROP DATABASE (your database)

command.

Something like:

using (SqlConnection con = new SqlConnection("server=yourserver;database=master;integrated security=SSPI"))
{
    using (SqlCommand cmd = new SqlCommand("DROP DATABASE (your database)", con))
    {
        try
        {
            con.Open();
            int returnValue = cmd.ExecuteNonQuery();
            con.Close();
        }
        catch (Exception exc)
        {
            string errorMsg = string.Format("{0}: {1}", exc.GetType().FullName, exc.Message);   
        }
    }
}

Or then you need to iterate over all tables - there's no single command to drop all tables at once. Also, when dropping tables, you might need to drop constraints and indexes first.

Dropping everything isn't exactly an easy job!

Answer 2

If you want to delete the tables without dropping the database you can also use the following command:

exec sp_MSforeachtable 'DROP TABLE ?'

Beware that you have to disable/delete all Foreign Key Constraints first, or it will most likely fail.