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.
how i drop all the tables if there is no schema
ReplyDeleteIn 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.
DeleteIf 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.