How to TRUNCATE all tables from aspnet schema?

Question

I've been doing alot of testing with my new MVC App and now I'm ready to move on to a new phase of development.

I would like to TRUNCATE all my aspnet generated tables, but I can't seem to figure out the order to remove all the foreign key constraints.

TRUNCATE TABLE  aspnet_Profile 
TRUNCATE TABLE  aspnet_UsersInRoles 
TRUNCATE TABLE  aspnet_PersonalizationPerUser
TRUNCATE TABLE  aspnet_Membership
TRUNCATE TABLE  aspnet_users

On this, I'm getting a foreign key constraint issue on aspnet_Users. Looking at the diagram, I can't seem to find any more that would reference aspnet_users.

I actually pulled this order from a delete statement I found online, which works fine user-by-user.

DECLARE @UserId uniqueidentifier
SET @UserId = 'f0a05439-5218-4668-b11d-21a43385ea2f'

DELETE FROM aspnet_Profile WHERE UserID = @UserId
DELETE FROM aspnet_UsersInRoles WHERE UserID = @UserId
DELETE FROM aspnet_PersonalizationPerUser WHERE UserID = @UserId
DELETE FROM dbo.aspnet_Membership WHERE UserID = @UserId
DELETE FROM aspnet_users WHERE UserID = @UserId

Which, I might be able to rework to get it to delete all users, but I would much rather TRUNCATE.

How often are you doing this that the savings you get with `TRUNCATE` will really play a big role in anything? — Aaron Bertrand, Jun 24 '12 at 17:48
Negligible. But it's something I wanted to do and couldn't, then couldn't figure out or find a answer to "why" and asked a question to learn. :) — mawburn, Jun 24 '12 at 17:54
Wouldn't your `DELETE` still have to process the tables in a defined order (whether or not you limited it to a single user)? The order you found online will work even if you exclude all the `WHERE` clauses (seems silly to loop through all the users and run 5 deletes for every user). — Aaron Bertrand, Jun 24 '12 at 18:26

score 4 · Accepted Answer · answered Jun 24 '12 at 17:47

You can't TRUNCATE a table with a foreign key reference to it. Even if both tables are empty and even if you disable the foreign key constraint.

Assuming you have the script to generate the constraints again, you can just drop the foreign keys from referencing tables, e.g.:

SELECT '
  ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id))
   + '.' + QUOTENAME(OBJECT_NAME(fk.parent_object_id))
   + ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS t
ON fk.referenced_object_id = t.[object_id]
WHERE t.name LIKE 'aspnet[_]%';

SELECT 'TRUNCATE TABLE ' + QUOTENAME(name) + ';'
  FROM sys.tables 
  WHERE name LIKE 'aspnet[_]%';

Otherwise it is probably safer to just use DELETE instead of TRUNCATE (and then DBCC CHECKIDENT if your goal is to also restart any IDENTITY values at 1).

How to TRUNCATE all tables from aspnet schema?

1 Answers1