How to Empty a SQL Database

SQL Completely Empty Database

Sounds like a job for Drop Database:

Warning: It doesn't EMPTY the database as the question asks, but it DELETES it, so be sure you have permissions to create a new one.

-- SQL Server Syntax  
DROP DATABASE [ IF EXISTS ] { database_name | database_snapshot_name } [ ,...n ] [;]

How to empty a SQL database?

Dump the schema using pg_dump. drop the database, recreate it and load the schema.

Dump you database schema (the -s tag) to a file:

pg_dump -s -f db.dump DB-NAME

Delete the database:

dropdb DB-NAME

Recreate it:

createdb DB-NAME

Restore the schema only:

pg_restore db.dump > psql DB-NAME

This should work on PostgreSQL; Other DBMS might have their own tools for that. I do no know of any generic tool to do it.

EDIT:

Following comments, you might want to skip the dropdb command, and simply create another database with the dumped schema. If all went through well, you can drop the old database:

pg_dump -s -f db.dump DB-NAME
createdb DB-NEW-NAME
pg_restore db.dump > psql DB-NEW-NAME

At this point, you have the full database at DB-NAME, and an empty schema at DB-NEW-NAME. after you're sure everything is OK, use dropdb DB-NAME.

Delete all data in SQL Server database

SQLMenace's solution worked for me with a slight tweak to how data is deleted - DELETE FROM instead of TRUNCATE.

-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 
GO 

EXEC sp_MSForEachTable 'DELETE FROM ?' 
GO 

-- enable referential integrity again 
EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL' 
GO

How to delete the contents of a database in SQL Server Management Studio?

Try:

step1> generate Script of the database and save it in file(.sql)(without data)

step2> delete current database

step3> run the script(.sql) in Sql Server Management Studio

now you'll get the blank database.

see Generate a Script (SQL Server Management Studio)

SQL create empty database

Make sure you have not modified model database

The model database is used as the template for all databases created on an instance of SQL Server

Empty the contents of a database

In SQL Server, there is no native way to do what you're asking. You do have a few options depending on your particular environment limitations:

Figure out the relationships between the tables and start deleting rows out in the appropriate order from foreigns to parents. This may be time-consuming for a large number of objects, but is the "safest" in terms of least destruction.
Disable the foreign key constraints and TRUNCATE TABLE. This will be a bit faster if you're dealing with lots of data, but you still have to to know where all your relationships are. Not too terrible if you're working with fewer tables, though option 1 becomes just as viable
Script out the database objects and DROP DATABASE/CREATE DATABASE. If you don't care about a raw teardown of the database, this is another option, however, you'll still need to be aware of object precedence for creation. SQL Server—as well as third-party tools— offer ways to script object DROP/CREATE. If you decide to go this route, the upside is that you have a scripted backup of all the objects (which I like to keep "just in case") and future tear-downs are nearly instantaneous as long as you keep your scripts synchronized with any changes.

As you can see, it's not a terribly simple process because you're trying to subvert the very reason for the existence of the constraints.

What is the fastest way to clear a SQL table?

Best way to clear a table is with TRUNCATE.

Since you are creating and droping ill assume you have no constraints.

TRUNCATE TABLE <target table>

Some advantages:

Less transaction log space is used.

The DELETE statement removes rows one at a time and records an entry
in the transaction log for each deleted row. TRUNCATE TABLE removes
the data by deallocating the data pages used to store the table data
and records only the page deallocations in the transaction log.

Fewer locks are typically used.

When the DELETE statement is executed using a row lock, each row in
the table is locked for deletion. TRUNCATE TABLE always locks the
table (including a schema (SCH-M) lock) and page but not each row.

Without exception, zero pages are left in the table.

After a DELETE statement is executed, the table can still contain
empty pages. For example, empty pages in a heap cannot be deallocated
without at least an exclusive (LCK_M_X) table lock. If the delete
operation does not use a table lock, the table (heap) will contain
many empty pages. For indexes, the delete operation can leave empty
pages behind, although these pages will be deallocated quickly by a
background cleanup process.

How to drop all tables and reset an Azure SQL Database

Since there is not an API way to do this that I am aware of, we have used this script to leverage a T-SQL query to clear the database.

To delete each table (and maintain your EF migration histories if you want)

while(exists(select 1 from INFORMATION_SCHEMA.TABLES 
             where TABLE_NAME != '__MigrationHistory' 
             AND TABLE_TYPE = 'BASE TABLE'))
begin
 declare @sql nvarchar(2000)
 SELECT TOP 1 @sql=('DROP TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME
 + ']')
 FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_NAME != '__MigrationHistory' AND TABLE_TYPE = 'BASE TABLE'
exec (@sql)
 /* you dont need this line, it just shows what was executed */
 PRINT @sql
end

To remove the foreign keys first if you need to

while(exists(select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE='FOREIGN KEY'))
begin
 declare @sql nvarchar(2000)
 SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME
 + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
 FROM information_schema.table_constraints
 WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
 exec (@sql)
 PRINT @sql
end

In my testing, this worked without issues (except I did not have the where clause in the DROP TABLE while query since I don't use Code First or EF migrations).

delete all from table

You can use the below query to remove all the rows from the table, also you should keep it in mind that it will reset the Identity too.

TRUNCATE TABLE table_name