删除多个表中记录的脚本

您必须使用动态 SQL 查询来实现这一点

1-首先，您必须在临时表中列出所有具有相应数据库的表 2-为包含Students和WinnersdataTable的数据库构建查询

你的脚本应该看起来像

--Get all Databases With corresponding Database

declare @SQL nvarchar(max)
DECLARE @strQuery AS NVARCHAR(MAX)

SET @strQuery = ''

CREATE TABLE #TblTemp (DatabaseName Varchar(255), Tablename Varchar(255))



set @SQL = (select 'union all 
select '''+D.name+''' as DatabaseName,
       T.name collate database_default as TableName
from '+quotename(D.name)+'.sys.tables as T
'
from sys.databases as D
for xml path(''), type).value('substring((./text())[1], 13)', 'nvarchar(max)')

--print @SQL
INSERT INTO #TblTemp
exec (@SQL)

-- Building Queries

SELECT @strQuery = @strQuery + 'Delete T1 from [' + name  + '].dbo.Students As T2
                        Inner join  [' + name  + '].dbo.Winners as T1 
                        On T1.[st_ID] = T2.[ID] 
                        Where    T1.[name] = IN(''Roy'',''Peter'')  ;

DELETE FROM [' + name  + '].dbo.Students WHERE [name] = IN(''Roy'',''Peter'') ;

'

 from sys.databases
 WHERE EXISTS (SELECT 1 FROM #TblTemp WHERE #TblTemp.DatabaseName = name AND #TblTemp.TableName = 'Students') AND
 EXISTS (SELECT 1 FROM #TblTemp WHERE #TblTemp.DatabaseName = name AND #TblTemp.TableName = 'Winners')

--VIEW QUERIES (you can copy result and execute it manually)
SELECT @strQuery



--EXECUTE QUERIES
EXEC(@strQuery)

--DROP Temp Table 
DROP TABLE #TblTemp

这将导致如下查询（如果这些数据库包含Students和Winners表）

Delete T1 from [master].dbo.Students As T2   Inner join  [master].dbo.Winners as T1    On T1.[st_ID] = T2.[ID]    Where    T1.[name] = IN('Roy','Peter')  ;     
DELETE FROM [master].dbo.Students WHERE [name] = IN('Roy','Peter') ;     
Delete T1 from [tempdb].dbo.Students As T2   Inner join  [tempdb].dbo.Winners as T1    On T1.[st_ID] = T2.[ID]    Where    T1.[name] = IN('Roy','Peter')  ;     
DELETE FROM [tempdb].dbo.Students WHERE [name] = IN('Roy','Peter') ;     
Delete T1 from [model].dbo.Students As T2   Inner join  [model].dbo.Winners as T1    On T1.[st_ID] = T2.[ID]    Where    T1.[name] = IN('Roy','Peter')  ;     
DELETE FROM [model].dbo.Students WHERE [name] = IN('Roy','Peter') ;     
Delete T1 from [msdb].dbo.Students As T2   Inner join  [msdb].dbo.Winners as T1    On T1.[st_ID] = T2.[ID]    Where    T1.[name] = IN('Roy','Peter')  ;     
DELETE FROM [msdb].dbo.Students WHERE [name] = IN('Roy','Peter') ;     
Delete T1 from [AdventureWorks2008R2].dbo.Students As T2   Inner join  [AdventureWorks2008R2].dbo.Winners as T1    On T1.[st_ID] = T2.[ID]    Where    T1.[name] = IN('Roy','Peter')  ;     DELETE FROM [AdventureWorks2008R2].dbo.Students WHERE [name] = IN('Roy','Peter') ;     Delete T1 from [DbMail].dbo.Students As T2   Inner join  [DbMail].dbo.Winners as T1    On T1.[st_ID] = T2.[ID]    Where    T1.[name] = IN('Roy','Peter')  ;     

如果这是您在下面的问题中所要求的，我现在不知道（您的问题不像这个问题那样具体，所以我的回答是提供一般的动态 sql 逻辑）

• 用 where 子句截断

您将不得不明确列出您需要从中删除的表。

其中一种情况是创建一个表，其中包含要从中删除的表列表，并根据具有特定姓名的学生应用适当delete的语句遍历每个表。id's

要遍历数据库中的所有表，您可以使用如下内容：

DECLARE c_tables CURSOR
FOR SELECT table_name
    FROM INFORMATION_SCHEMA.TABLES;

DECLARE
  @table_name VARCHAR(100);

OPEN c_tables;

FETCH NEXT FROM c_tables INTO
  @table_name;

WHILE @@FETCH_STATUS=0
BEGIN
--<your command here>
FETCH NEXT FROM c_tables INTO
  @table_name;
END;

CLOSE c_tables;

DEALLOCATE c_tables;

并且不要忘记delete在测试环境中进行测试。

我认为正确的方法是首先从没有动态 sql 的学生表中删除记录并将删除的 ID 存储在临时表中。

像这样，

DECLARE @DeletedSTID table(id int not null)

delete from Students
output deleted.id into @DeletedSTID
where name in('Peter','Roy')

select * from @DeletedSTID

在上面的示例中，我不必每次都使用“where name in()”。我使用它一次并将其存储在临时表中。

例如我正在使用 Adventure DB，

USE AdventureWorks2012
GO

CREATE TABLE #DeletedSTID (id INT NOT NULL)

CREATE TABLE #temp (tablename VARCHAR(100))

DECLARE @Sql VARCHAR(max) = ''

BEGIN TRY
    BEGIN TRANSACTION

    DELETE
    FROM HumanResources.EmployeeDepartmentHistory
    OUTPUT deleted.BusinessEntityID
    INTO #DeletedSTID
    WHERE BusinessEntityID IN (
            1
            ,2
            )

    INSERT INTO #temp
    SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME = 'BusinessEntityID'
        AND TABLE_NAME NOT LIKE 'v%'

    SELECT *
    FROM #DeletedSTID

    SELECT *
    FROM #temp

    SELECT @Sql = @Sql + ' delete from ' + tablename + ' a where exists 
(select id from #DeletedSTID b where b.id=a.BusinessEntityID)  '
    FROM #temp

    PRINT @Sql

    EXEC (@Sql)

    ROLLBACK
        --COMMIT
END TRY

BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK

    SELECT ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

DROP TABLE #temp

DROP TABLE #DeletedSTID

从SQL 删除

大多数数据库管理系统允许您创建外键约束，以便如果您删除表中的一行，相关表的相应行也会自动删除。这确保了数据的完整性。

和

在 SO 中回答。（必须适应你的情况）

你不能用分号分隔它们吗？

Delete from messages where messageid = '1';
Delete from usersmessages where messageid = '1'

或者

只需使用 INNER JOIN 如下

DELETE messages , usersmessages  FROM messages  INNER JOIN usersmessages  
WHERE messages.messageid= usersmessages.messageid and messages.messageid = '1'

编辑由于@NIC的评论（和DV？）：

第一个选项： table_constraint (Transact-SQL)

删除时 { 无操作 | 级联 | 设置空 | SET DEFAULT } 指定表中被更改的行发生的操作，如果这些行具有引用关系并且引用的行从父表中删除。默认为无操作。

...

CASCADE 如果从父表中删除该行，则从引用表中删除相应行。

...

和SO 中的一个例子

第二种选择：

准备桌子

DROP TABLE IF EXISTS dbo.Student, dbo.Winner

CREATE TABLE dbo.Student 
(
    ID          INT         NOT NULL,
    name        VARCHAR(30) NOT NULL,
    class       VARCHAR(30) NOT NULL
);
CREATE TABLE dbo.Winner
(
    stID        INT         NOT NULL,
    achievement INT         NOT NULL
);
GO

INSERT INTO dbo.Student(ID, name, class)
VALUES
    (1, 'Roy', '2'),
    (2, 'James', '2'),
    (3, 'Carl', '2'),
    (4, 'Peter', '2'),
    (5, 'Alice', '2')

INSERT INTO dbo.Winner(stID, achievement)
VALUES
    (1, 1),
    (2, 1),
    (3, 3),
    (4, 5),
    (5, 5)

删除行

DELETE FROM Student WHERE ID = 1;
DELETE FROM Winner WHERE stID = 1

在 SQL Server 13.0.1722.0 中测试 注意：DROP TABLE IF EXIST 可能在旧版本中不起作用