Skip to content
On this page

An image

Drop All Tables Of A Database

This is a SQL code block written in Microsoft SQL Server.

The code block declares a variable @sql of type NVARCHAR(max) and initializes it to an empty string.

The SELECT statement retrieves the names of all the base tables in the current database using the INFORMATION_SCHEMA.TABLES view. For each table, it concatenates a DROP TABLE statement with the fully qualified table name (i.e., including the schema name) and appends it to the @sql variable using the += operator. The QUOTENAME function is used to properly escape and delimit the table and schema names, which helps to avoid SQL injection attacks.

Finally, the sp_executesql system stored procedure is called with the @sql variable as its argument to execute the dynamically generated SQL code and drop all the tables that were selected by the SELECT statement.


SELECT @sql += ' Drop table ' + QUOTENAME(TABLE_SCHEMA) + '.'+ QUOTENAME(TABLE_NAME) + '; '

Exec Sp_executesql @sql

Last updated: