Delete all records from a database except a user

Question

Delete all records from a database except a user

Navigation

#1 by (1 votes)

1

I have a database that I need to delete all the records that exist in it, except a user who is in the User table with a link to another table ( AspNetUsers ). I have already made a few attempts at SSMS , but I always encounter references and conflicts.

For example, I ran the following command:

USE [dbTeste]
GO

DELETE FROM [dbo].[AspNetUsers]
  WHERE Id !='52252ba2-8312-4650-b829-c611e2c3cfdb'
  GO

Message 547, Level 16, State 0, Line 4 The DELETE statement conflicted with the REFERENCE constraint "FK_dbo.Usuario_dbo.AspNetUsers_ApplicationUser_Id". The conflict occurred in database "matina", table "dbo.Usuario", column 'ApplicationUser_Id'. The statement has been terminated.

I just need to keep this user, which I use to login and delete all the records from the other tables.

sql sql-server ssms

asked by anonymous 26.11.2018 / 16:50

1 answer

Drag and Drop eating images Select with two fields as FKs of the same table

score 1 · Accepted Answer

You can use the to generate the changes to key to include ON DELETE CASCADE that will automatically delete the row with the foreign key set when the primary key of the table being referenced is deleted.

SELECT DropStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema + '].[' + ForeignKeys.ForeignTableName + '] DROP CONSTRAINT [' + ForeignKeys.ForeignKeyName + ']; ',
       CreateStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema + '].[' + ForeignKeys.ForeignTableName + '] WITH CHECK ADD CONSTRAINT [' +  ForeignKeys.ForeignKeyName + '] FOREIGN KEY([' + ForeignKeys.ForeignTableColumn + ']) REFERENCES [' + SCHEMA_NAME(sys.objects.schema_id) + '].[' + sys.objects.name + ']([' + sys.columns.name + ']) ON DELETE CASCADE; '
  FROM sys.objects
 INNER JOIN sys.columns ON sys.columns.object_id = sys.objects.object_id
 INNER JOIN (
   SELECT sys.foreign_keys.name as ForeignKeyName,
          SCHEMA_NAME(sys.objects.schema_id) as ForeignTableSchema,
          sys.objects.name as ForeignTableName,
          sys.columns.name  as ForeignTableColumn,
          sys.foreign_keys.referenced_object_id as referenced_object_id,
          sys.foreign_key_columns.referenced_column_id as referenced_column_id,
     FROM sys.foreign_keys
    INNER JOIN sys.foreign_key_columns ON sys.foreign_key_columns.constraint_object_id = sys.foreign_keys.object_id
    INNER JOIN sys.objects ON sys.objects.object_id = sys.foreign_keys.parent_object_id
    INNER JOIN sys.columns ON sys.columns.object_id = sys.objects.object_id
                          AND sys.columns.column_id = sys.foreign_key_columns.parent_column_id
 ) ForeignKeys ON ForeignKeys.referenced_object_id = sys.objects.object_id
              AND ForeignKeys.referenced_column_id = sys.columns.column_id
 WHERE sys.objects.type = 'U'
   AND sys.objects.name NOT IN ('sysdiagrams')
   AND sys.objects.name = 'AspNetUsers'

Attention to the fact that query does not support composite keys.

After running the change scripts it should be possible to run DELETE in the way you mentioned.