Jan 3, 2012

Sql query to drop all the tables of a database of given schema in sql server


Sql query or script to drop or delete all the tables of a database of given or specified schema in sql server

EXECUTE sp_MSforeachtable  @command1 = N'DROP TABLE ?', @whereand = N'AND schema_id = (SELECT schema_id FROM sys.schemas WHERE name = ''dbo'')'

Another script:

EXECUTE sp_MSforeachtable  @command1 = N'DROP TABLE ?', @whereand = N'AND schema_id = SCHEMA_ID(''dbo'')'

This query will drop the all the tables of schema dbo in current database.

2 comments:

  1. how i drop all the tables if there is no schema

    ReplyDelete
    Replies
    1. In sql server all tables must comes under any schema. If you don't specify any schema then it comes under default schema of your database. In general default schema of a database is dbo.

      If you want to drop all tables of database without specifying schema use this sql statement:

      EXECUTE sp_MSforeachtable N'DROP TABLE ?'

      It will drop all the tables of default schema of current database.

      Delete