刪除多個表中記錄的腳本

我想從兩個或多個相互連結的表中刪除特定記錄。

例如：我有兩張桌子，Students 和 Winners 我想一次性從兩張桌子上刪除 Roy 和 Peter 的名字。

表：學生

> ID     name      class
> 1      Roy         2
> 2      James       3
> 3      Carl        4
> 4      Peter       4
> 5      Alice       5

表：獲獎者

St_ID          achievement
1              1
2              1
3              3
4              5
5              5

我有 100 多個表，其中 50 條特定記錄要從所有表中刪除。

您必須使用動態 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 子句截斷

引用自：https://dba.stackexchange.com/questions/171254