How to delete all rows from all tables in a SQL Server database?
12 Answers
Note that TRUNCATE won't work if you have any referential integrity set.
In that case, this will work:
EXEC sp_MSForEachTable 'DISABLE TRIGGER ALL ON ?'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'SET QUOTED_IDENTIFIER ON; DELETE FROM ?'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'ENABLE TRIGGER ALL ON ?'
GO
Edit: To be clear, the ? in the statements is a ?. It's replaced with the table name by the sp_MSForEachTable procedure.
If you just have constraints just paste this lines in the query and run it
EXEC sys.sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sys.sp_msforeachtable 'DELETE FROM ?'
EXEC sys.sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
You could delete all the rows from all tables using an approach like Rubens suggested, or you could just drop and recreate all the tables. Always a good idea to have the full db creation scripts anyway so that may be the easiest/quickest method.
This answer builds on Zach Smith's answer by resetting the identity column as well:
- Disabling all constraints
- Iterating through all tables except those you choose to exclude
- Deletes all rows from the table
- Resets the identity column if one exists
- Re-enables all constraints
Here is the query:
-- Disable all constraints in the database
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
declare @catalog nvarchar(250);
declare @schema nvarchar(250);
declare @tbl nvarchar(250);
DECLARE i CURSOR LOCAL FAST_FORWARD FOR select
TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where
TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME != 'sysdiagrams'
AND TABLE_NAME != '__RefactorLog'
-- Optional
-- AND (TABLE_SCHEMA = 'dbo')
OPEN i;
FETCH NEXT FROM i INTO @catalog, @schema, @tbl;
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @sql NVARCHAR(MAX) = N'DELETE FROM [' + @catalog + '].[' + @schema + '].[' + @tbl + '];'
/* Make sure these are the commands you want to execute before executing */
PRINT 'Executing statement: ' + @sql
--EXECUTE sp_executesql @sql
-- Reset identity counter if one exists
IF ((SELECT OBJECTPROPERTY( OBJECT_ID(@catalog + '.' + @schema + '.' + @tbl), 'TableHasIdentity')) = 1)
BEGIN
SET @sql = N'DBCC CHECKIDENT ([' + @catalog + '.' + @schema + '.' + @tbl + '], RESEED, 0)'
PRINT 'Executing statement: ' + @sql
--EXECUTE sp_executesql @sql
END
FETCH NEXT FROM i INTO @catalog, @schema, @tbl;
END
CLOSE i;
DEALLOCATE i;
-- Re-enable all constraints again
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
--Load tables to delete from
SELECT
DISTINCT
' Delete top 1000000 from <DBName>.<schema>.' + c.TABLE_NAME + ' WHERE <Filter Clause Here>' AS query,c.TABLE_NAME AS TableName, IsDeleted=0, '<InsertSomeDescriptorHere>' AS [Source]--,t.TABLE_TYPE, c.*
INTO dbo.AllTablesToDeleteFrom
FROM INFORMATION_SCHEMA.TABLES AS t
INNER JOIN information_schema.columns c ON c.TABLE_NAME = t.TABLE_NAME
WHERE c.COLUMN_NAME = '<column name>'
AND c.TABLE_SCHEMA = 'dbo'
AND c.TABLE_CATALOG = '<DB Name here>'
AND t.TABLE_TYPE='Base table'
--AND t.TABLE_NAME LIKE '<put filter here>'
DECLARE @TableSelect NVARCHAR(1000)= '';
DECLARE @Table NVARCHAR(1000)= '';
DECLARE @IsDeleted INT= 0;
DECLARE @NumRows INT = 1000000;
DECLARE @Source NVARCHAR(50)='';
WHILE ( @IsDeleted = 0 )
BEGIN
--This grabs one table at a time to be deleted from. @TableSelect has the sql to execute. it is important to order by IsDeleted ASC
--because it will pull tables to delete from by those that have a 0=IsDeleted first. Once the loop grabs a table with IsDeleted=1 then this will pop out of loop
SELECT TOP 1
@TableSelect = query,
@IsDeleted = IsDeleted,
@Table = TableName,
@Source=[a].[Source]
FROM dbo.AllTablesToDeleteFrom a
WHERE a.[Source]='SomeDescriptorHere'--use only if needed
ORDER BY a.IsDeleted ASC;--this is required because only those records returned with IsDeleted=0 will run through loop
--SELECT @Table; can add this in to monitor what table is being deleted from
WHILE ( @NumRows = 1000000 )--only delete a million rows at a time?
BEGIN
EXEC sp_executesql @TableSelect;
SET @NumRows = @@ROWCOUNT;
--IF @NumRows = 1000000 --can do something here if needed
--One wants this loop to continue as long as a million rows is deleted. Once < 1 million rows is deleted it pops out of loop
--and grabs next table to delete
-- BEGIN
--SELECT @NumRows;--can add this in to see current number of deleted records for table
INSERT INTO dbo.DeleteFromAllTables
( tableName,
query,
cnt,
[Source]
)
SELECT @Table,
@TableSelect,
@NumRows,
@Source;
-- END;
END;
SET @NumRows = 1000000;
UPDATE a
SET a.IsDeleted = 1
FROM dbo.AllTablesToDeleteFrom a
WHERE a.TableName = @Table;
--flag this as deleted so you can move on to the next table to delete from
END;
Related