CODEWITHSUNDEEP
asp.net-mvct-sqlsql-server-2008-r2database-schema
mawburn
mawburn

Reputation: 2346

How to TRUNCATE all tables from aspnet schema?

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.

Upvotes: 1

Views: 2649

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280431

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).

Upvotes: 4

Related Questions