Delete rows from multiple tables using a single query (SQL Express 2005) with a WHERE condition

Use this in procedure

declare cu cursor for SELECT [name] FROM sys.Tables where [name] like 'tbl_%'
declare @table varchar(100)
declare @sql nvarchar(1000)

OPEN cu  
FETCH NEXT FROM cu INTO @table 

WHILE @@FETCH_STATUS = 0  
BEGIN  
    set @sql = N'delete from ' + @table
    EXEC sp_executesql @sql
    FETCH NEXT FROM cu INTO @table 
END   
CLOSE cu;  
DEALLOCATE cu;

I use this for cleaning up data in test/development databases. You can filter by table name and record count.

DECLARE @sqlCommand VARCHAR(3000);
DECLARE @tableList TABLE(Value NVARCHAR(128));
DECLARE @TableName VARCHAR(128);
DECLARE @RecordCount INT;

-- get a cursor with a list of table names and their record counts
DECLARE MyCursor CURSOR FAST_FORWARD
FOR SELECT t.name TableName,
           i.rows Records
    FROM sysobjects t,
         sysindexes i
    WHERE 
          t.xtype = 'U'              -- only User tables
          AND i.id = t.id          
          AND i.indid IN(0, 1)       -- 0=Heap, 1=Clustered Index
          AND i.rows < 10            -- Filter by number of records in the table
          AND t.name LIKE 'Test_%';  -- Filter tables by name. You could also provide a list:
                                     -- AND t.name IN ('MyTable1', 'MyTable2', 'MyTable3');
                                     -- or a list of tables to exclude:
                                     -- AND t.name NOT IN ('MySpecialTable', ... );

OPEN MyCursor;

FETCH NEXT FROM MyCursor INTO @TableName, @RecordCount;

-- for each table name in the cursor, delete all records from that table:
WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sqlCommand = 'DELETE FROM ' + @TableName;
        EXEC (@sqlCommand);
        FETCH NEXT FROM MyCursor INTO @TableName, @RecordCount;
    END;

CLOSE MyCursor;
DEALLOCATE MyCursor;

Reference info:

• sysobjects
• sysindexes
• SQL Server Cursors

Generally I do deletions from multiple tables with one query. It works correct with PostgreSQL, but doesn't work for MSSQL, and that's why I got here.
With Postgres (I don't know about other DBs) you can do:

WITH obsolete_ids AS (
    SELECT pr_r."ID" AS ids
    FROM "PULL_REQUEST" pr_r
    WHERE 1=1
    AND pr_r."STATUS" IN (1)
) , del_commit_junc AS (
DELETE 
FROM "PR_TO_COMMIT"
WHERE "REQUEST_ID" IN (SELECT ids FROM obsolete_ids)
)
DELETE
FROM "PULL_REQUEST" pr_out
WHERE pr_out ."ID" IN (SELECT ids FROM obsolete_ids)

Actually In my original Query I delete foreign keys from 2 more tables, but here I just paste an example. That way I solved problem with Foreign Keys in "PR_TO_COMMIT" table.

The way I am using to Delete rows from multiple tables in SQL Server? The most important is to use on delete cascade when creating a foreign key in the table

#Table 1 Create:# 
    
create table Customer_tbl 
(
C_id int primary key, 
C_Name varchar(50), 
C_Address varchar(max), 
City varchar(50)
);


#Table 2: Create with Foreign Key Constraints#

create table [order] 
(
Ord_Id int primary key, 
Item varchar(50), 
Quantity int, 
Price_Of_1 int, 
C_id int foreign key references Customer_tbl(C_id)
on delete cascade

);

delete from Customer_tbl where C_id = 2;

$qry = "DELETE lg., l. FROM lessons_game lg RIGHT JOIN lessons l ON lg.lesson_id = l.id WHERE l.id = ?";

lessons is Main table and lessons_game is subtable so Right Join