Category Archives: Database

TIP: Quickly purge SQL Server Data

I was working on writing some integration tests, and needed to quickly get the database to a clean state between them. Dropping and recreating the database would take too long,┬ábut, if I could easily delete all the data, then I could populate with what I needed for each test…with MS SQL Server, you can use the stored proc sp_MSForEachTable to run SQL against each table. The following code uses the proc, disabling triggers, and constraints so it can delete all data, then enables them again

public static void PurgeData()
{
	using (var conn = new SqlConnection(ConnectionString))
	{
		conn.Open();
 
		var cmdText = new StringBuilder();
		cmdText.AppendLine("EXEC sp_MSForEachTable 'DISABLE TRIGGER ALL ON ?'");
		cmdText.AppendLine("EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'");
		cmdText.AppendLine("EXEC sp_MSForEachTable 'DELETE FROM ?'");
		cmdText.AppendLine("EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'");
		cmdText.AppendLine("EXEC sp_MSForEachTable 'ENABLE TRIGGER ALL ON ?'");
 
		using (var sqlCmd = new SqlCommand(cmdText.ToString(), conn))
		{
			sqlCmd.ExecuteNonQuery();
		}
 
		conn.Close();
	}
}

Hope this helps you out.