Delete all rows in all table that reference to user table with same where condition table-CodePudding

I want to delete all rows of all tables that reference the user table in SQL Server

User Table Name : security.User
User Table PK : UserId

My query for table

Delete From FKschema.FKTable where FKColumn not in (1,2)

How can I do this with one query for all tables?

CodePudding user response：

You should use CURSOR and create string and execute it

DECLARE @tableName NVARCHAR(100), @schema NVARCHAR(50), @refcolumn NVARCHAR(50)
DECLARE @Query NVARCHAR(max)= ' DELETE from %s.%s where %s not in (1,2)'


DECLARE Cur CURSOR FOR
SELECT object_name(fk.parent_object_id) as Table_Name,SCHEMA_NAME(schema_id),(SELECT name FROM sys.columns c WHERE c.column_id=fk_cols.parent_column_id AND c.object_id=fk.parent_object_id) AS columnName 
FROM sys.foreign_keys fk
inner join sys.foreign_key_columns fk_cols
    on fk_cols.constraint_object_id = fk.object_id
WHERE fk.referenced_object_id = object_id('security.users','U');



OPEN Cur
FETCH NEXT FROM Cur INTO @tableName, @schema,@refcolumn
WHILE @@FETCH_STATUS=0
BEGIN
    DECLARE @text NVARCHAR(max)=FORMATMESSAGE(@Query, @schema,@tableName,@refcolumn) 
    EXECUTE(@text)
    FETCH NEXT FROM Cur INTO @tableName, @schema,@refcolumn
    PRINT @text
END

CLOSE Cur
DEALLOCATE Cur

CodePudding user response：

When we set a foreignkey to a table, we also provide another attribute (in django it is on_delete=models.CASCADE) which means that if the parent is deleted delete the relation too.

E g,

CREATE TABLE inventory
    ( inventory_id INT PRIMARY KEY,
    CONSTRAINT fk_inv_product_id
    FOREIGN KEY (product_id)
    REFERENCES products (product_id)

   ON DELETE CASCADE // this is what you need
   );