How do I create and use a stored procedure for SQL-Sever which will delete the data in all tables in a database with VS 2010?

Question

I've been using the entities framework with ASP.NET MVC and I'm looking for an easy and fast way to drop all of the information in the database. It takes quite a while to delete all of the information from the entities object and then save the changes to the database (probably because there are a lot of many-to-many relationships) and I think it should be really fast to just remove all of the information with a stored procedure but I'm not sure how to go about this. How do I create and use a stored procedure for SQL-Sever which will delete the data in all tables in a database with VS 2010? Also if I do this will the command be compatible with other version of SQL-Server? (I'm using 2008 on my testing comptuer, but when I upload it I not sure if my hosting company uses 2008 or 2005).

Thanks!!

Answer 1

This solution will work well in terms of deleting all your data in your database's tables.

You can create this stored proc right within Visual Studio on your SQL Server 2008 development server. It'll work well in any version of SQL Server (2000+).

 CREATE PROC NukeMyDatabase
 AS

    --order is important here. delete data in FK'd tables first.
    DELETE Foo
    DELETE Bar

    TRUNCATE TABLE Baz

I prefer TRUNCATE TABLE, as it's faster. It'll depend on your data model, as you can't issue a TRUNCATE TABLE on a table referenced by a foreign key constraint (i.e. parent tables).

You could then call this stored proc using Entity Framework after adding it to your .edmx:

  myContext.NukeMyDatabase();

Answer 2

exec sp_MSForEachTable 'truncate table ?';

But I would recommend a different approach: take a backup of the empty database and simply restore this backup before each run. Even better, have no database at all and have your application be capable of deploying the database itself, using a schema version upgrade set of scripts.

Answer 3

I recently faced a similar problem in that I had to clear over 200+ tables that were interlinked through many foreign key constraints.

The critical issue, as p.campbell pointed out, is determining the correct order of DELETE statements.

The foreign key constraints between tables essentially represent a hierarchy. If table 3 is dependent on table 2, and table 2 is dependent on table 1, then table 1 is the root and table 3 is the leaf.

In other words, if your going to delete from these three tables, you have to start with the table that has no dependencies and work your way up. That is the intent of this code:

DECLARE @sql VARCHAR(MAX)
SET @sql = ''

;WITH c AS 
(
SELECT 
  parent_object_id AS org_child,
  parent_object_id,
  referenced_object_id,
  1 AS Depth
FROM sys.foreign_keys
UNION ALL
SELECT
  c.org_child,
  k.parent_object_id,
  k.referenced_object_id,
  Depth + 1
FROM c
INNER JOIN sys.foreign_keys k 
  ON c.referenced_object_id = k.parent_object_id
WHERE c.parent_object_id != k.referenced_object_id
),
c2 AS (
SELECT
  OBJECT_NAME(org_child) AS ObjectName,
  MAX(Depth) AS Depth
FROM c
GROUP BY org_child
UNION ALL
SELECT
  OBJECT_NAME(object_id),
  0 AS Depth
FROM sys.objects o
LEFT OUTER JOIN c 
  ON o.object_id = c.org_child 
WHERE c.org_child IS NULL
AND o.type = 'U'
) 
SELECT @sql = @sql + 'DELETE FROM ' + CAST(ObjectName AS VARCHAR(100)) 
  + ';' + CHAR(13) + CHAR(10) /** for readability in PRINT statement */
FROM c2
ORDER BY Depth DESC

PRINT @sql
/** EXEC (@sql) **/