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)
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
How to delete large data of table in SQL without log?
If you are Deleting All the rows in that table the simplest option is to Truncate table, something like
TRUNCATE TABLE LargeTable
GO
Truncate table will simply empty the table, you cannot use WHERE clause to limit the rows being deleted and no triggers will be fired.
On the other hand if you are deleting more than 80-90 Percent of the data, say if you have total of 11 million rows and you want to delete 10 million another way would be to Insert these 1 million rows (records you want to keep) to another staging table. Truncate this large table and Insert back these 1 million rows.
Or if permissions/views or other objects which has this large table as their underlying table doesn't get affected by dropping this table, you can get these relatively small amounts of the rows into another table, drop this table and create another table with same schema, and import these rows back into this ex-Large table.
One last option I can think of is to change your database's
Recovery Mode to SIMPLE
and then delete rows in smaller batches using a while loop something like this:DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;
WHILE (@Deleted_Rows > 0)
BEGIN
-- Delete some small number of rows at a time
DELETE TOP (10000) LargeTable
WHERE readTime < dateadd(MONTH,-7,GETDATE())
SET @Deleted_Rows = @@ROWCOUNT;
END
and don't forget to change the Recovery mode back to full and I think you have to take a backup to make it fully effective (the change or recovery modes).
How to drop all tables in a SQL Server database?
It doesn't work for me either when there are multiple foreign key tables.
I found that code that works and does everything you try (delete all tables from your database):
DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR
SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_SCHEMA + '].[' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + '];'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME
OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql
WHILE (@@FETCH_STATUS = 0)
BEGIN
Exec sp_executesql @Sql
FETCH NEXT FROM @Cursor INTO @Sql
END
CLOSE @Cursor DEALLOCATE @Cursor
GO
EXEC sp_MSforeachtable 'DROP TABLE ?'
GO
You can find the post here. It is the post by Groker.
Related Topics
How to Store Decimal Values in SQL Server
Icalendar "Field" List (For Database Schema Based on Icalendar Standard)
Best Way to Store Working Hours and Query It Efficiently
How to See Cakephp's SQL Dump in the Controller
How to Add a Column That Doesn't Allow Nulls in a Postgresql Database
What Should I Name a Table That Maps Two Tables Together
Sqlite String Contains Other String Query
Understanding How Join Works When 3 or More Tables Are Involved. [Sql]
How to Create Simple Fuzzy Search with Postgresql Only
Relationship of Primary Key and Clustered Index
SQL Server Plans:Difference Between Index Scan/Index Seek
Copy a Table (Including Indexes) in Postgres
Job Queue as SQL Table with Multiple Consumers (Postgresql)
Why Is There a Huge Performance Difference Between Temp Table and Subselect