For any number of reasons you might need to delete all rows of data from all tables in a SQL Server database. If you have many tables in a database this can become quite an effort if performed manually. Additionally, triggers and constraints which enforce referential integrity must be considered if DELETE or TRUNCATE statements are to succeed. This solution provides a stored procedure (works with SQL 2000/2005/2008) which will delete All data from All tables in the current database.
Please use with caution.
This can be very useful when you want to copy a database into a test or development environment but you don’t want to copy the data because of compliance or size constraints.
--EXEC DeleteAllData
CREATE PROCEDURE DeleteAllData
AS
BEGIN
DECLARE @SQL nvarchar(2000), @CurrentTable sysname, @CurrentSchema sysname
--Grab the server version for any statements which
--need to be modified based upon the server version
DECLARE @ServerVersion int
SET @ServerVersion = (SELECT CAST(LEFT
(CAST(SERVERPROPERTY('ProductVersion')
AS varchar(50)),
CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion')
AS varchar(50))) - 1) AS int))
--This solution entails a cursor. Alternatively, it could be done with
--the undocumented stored procedure sp_msforeachtable, or with loop logic.
DECLARE TableCursor SCROLL CURSOR FOR
SELECT QUOTENAME(TABLE_SCHEMA) AS schemaname,
QUOTENAME(TABLE_NAME) AS name
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @CurrentSchema, @CurrentTable
--Disable all triggers first
WHILE @@FETCH_STATUS = 0
BEGIN
--Create a TSQL string to disable triggers on the current table
SET @SQL =
(SELECT 'ALTER TABLE '+ @CurrentSchema
+ '.' + @CurrentTable
+ ' DISABLE TRIGGER ALL')
EXECUTE sp_ExecuteSQL @SQL;
--Print a success or failure message, depending
--upon whether or not an error was raised.
IF @@ERROR = 0
BEGIN
PRINT 'Triggers successfully disabled on '
+ @CurrentSchema + '.' + @CurrentTable
END
ELSE
BEGIN
PRINT 'An error has occured while disabling triggers on '
+ @CurrentSchema + '.' + @CurrentTable
END
--Create a TSQL string to disable constraints on the current table
SET @SQL =
(SELECT 'ALTER TABLE '+ @CurrentSchema
+ '.' + @CurrentTable
+ ' NOCHECK CONSTRAINT ALL')
EXECUTE sp_ExecuteSQL @SQL;
--Print a success or failure message, depending
--upon whether or not an error was raised.
IF @@ERROR = 0
BEGIN
PRINT 'Constraints successfully disabled on '
+ @CurrentSchema + '.' + @CurrentTable
END
ELSE
BEGIN
PRINT 'An error has occured while disabling constraints on '
+ @CurrentSchema + '.' + @CurrentTable
END
--Fetch the next table from the cursor
FETCH NEXT FROM TableCursor INTO @CurrentSchema, @CurrentTable
END
--Move back to the first table in the cursor
FETCH FIRST FROM TableCursor INTO @CurrentSchema, @CurrentTable
WHILE @@FETCH_STATUS = 0
BEGIN
IF @ServerVersion >= 9 --IF we're on SQL 2005 or greater, we can use Try/Catch.
BEGIN
SET @SQL = (SELECT 'BEGIN TRY
TRUNCATE TABLE ' + @CurrentSchema + '.' + @CurrentTable + '
PRINT ''Data successfully truncated from ' + @CurrentSchema + '.' + @CurrentTable + '''
END TRY
BEGIN CATCH
DELETE FROM ' + @CurrentSchema + '.' + @CurrentTable + '
IF EXISTS(SELECT ''A'' FROM information_schema.columns
WHERE COLUMNPROPERTY(OBJECT_ID(''' + @CurrentSchema + '.' + @CurrentTable + '''),
column_name,''IsIdentity'')=1 AND QUOTENAME(TABLE_SCHEMA) = ''' + @CurrentSchema + '''
AND QUOTENAME(TABLE_NAME) = ''' + @CurrentTable + ''')
BEGIN
DBCC CHECKIDENT(''' + @CurrentSchema + '.' + @CurrentTable + ''', RESEED, 0)
END
PRINT ''Data successfully deleted from ' + @CurrentSchema + '.' + @CurrentTable + '''
END CATCH')
END
ELSE --We're on SQL 2000, so we need to check for foreign key existence first.
BEGIN
SET @SQL = (SELECT 'IF OBJECTPROPERTY(OBJECT_ID(''' + @CurrentSchema + '.' + @CurrentTable + '''), ''TableHasForeignRef'') <> 1
BEGIN
TRUNCATE TABLE ' + @CurrentSchema + '.' + @CurrentTable + '
PRINT ''Data successfully truncated from ' + @CurrentSchema + '.' + @CurrentTable + '''
END
ELSE
BEGIN
DELETE FROM ' + @CurrentSchema + '.' + @CurrentTable + '
IF EXISTS(SELECT ''A'' FROM information_schema.columns
WHERE COLUMNPROPERTY(OBJECT_ID(''' + @CurrentSchema + '.' + @CurrentTable + '''),
column_name,''IsIdentity'')=1 AND QUOTENAME(TABLE_SCHEMA) = ''' + @CurrentSchema + '''
AND QUOTENAME(TABLE_NAME) = ''' + @CurrentTable + ''')
BEGIN
DBCC CHECKIDENT(''' + @CurrentSchema + '.' + @CurrentTable + ''', RESEED, 0)
END
PRINT ''Data successfully deleted from ' + @CurrentSchema + '.' + @CurrentTable + '''
END')
END
EXECUTE sp_ExecuteSQL @SQL;
--Fetch the next table from the cursor
FETCH NEXT FROM TableCursor INTO @CurrentSchema, @CurrentTable
END
--Move back to the first table in the cursor
FETCH FIRST FROM TableCursor INTO @CurrentSchema, @CurrentTable
WHILE @@FETCH_STATUS = 0
BEGIN
--Reenable triggers
SET @SQL = (SELECT 'ALTER TABLE ' + @CurrentSchema + '.' + @CurrentTable + ' ENABLE TRIGGER ALL')
EXECUTE sp_ExecuteSQL @SQL;
--Print a success or failure message, depending upon whether or not an error was raised.
IF @@ERROR = 0
BEGIN
PRINT 'Triggers successfully reenabled on ' + @CurrentSchema + '.' + @CurrentTable
END
ELSE
BEGIN
PRINT 'An error has occured while reenabling triggers on ' + @CurrentSchema + '.' + @CurrentTable
END
--Now reenable constraints
SET @SQL = (SELECT 'ALTER TABLE ' + @CurrentSchema + '.' + @CurrentTable + ' CHECK CONSTRAINT ALL')
EXECUTE sp_ExecuteSQL @SQL;
--Print a success or failure message, depending upon whether or not an error was raised.
IF @@ERROR = 0
BEGIN
PRINT 'Constraints successfully disabled on ' + @CurrentTable
END
ELSE
BEGIN
PRINT 'An error has occured while disabling constraints on ' + @CurrentTable
END
--Fetch the next table from the cursor
FETCH NEXT FROM TableCursor INTO @CurrentSchema, @CurrentTable
END
--CLOSE and DEALLOCATE our cursor
CLOSE TableCursor
DEALLOCATE TableCursor
END
Technology News and Information by SeniorDBA 
I just loop through all of the tables a couple of times until there’s nothing left to delete… 🙂
using (
var connection =
new SqlConnection(
System.Configuration.ConfigurationManager.ConnectionStrings[“DefaultConnection”].ConnectionString))
{
connection.Open();
var tables = new List();
using (var command = new SqlCommand(“”, connection))
{
command.CommandText = @”SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES’)”;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
tables.Add(reader[“TABLE_NAME”].ToString());
}
}
var attempt = 0;
var tryAgain = true;
while (attempt < 11 && tryAgain)
{
tryAgain = false;
foreach (var table in tables)
{
try
{
command.CommandText = string.Format("DELETE FROM {0}", table);
command.ExecuteNonQuery();
}
catch (Exception)
{
attempt++;
tryAgain = true;
}
}
}
}
connection.Close();
}
[Editor: You cannot truncate tables referenced by foreign constraints, you have to disable then first, which it what that script does. Your delete will fail.]
LikeLike
You can accomplish the same by using ‘Extract Data-tier Application’. (a DAC package can only be deployed to SQL Server 2008 R2)
LikeLike