从所有表格中通过ID删除记录

Question

我希望有一种快速的方法来删除与特定“主”记录相关的所有记录，包括“主”表和所有相关表。 这主要是为了方便地输入测试记录，然后删除所有痕迹，手动执行会很耗时，因为可能有数十个引用记录的表。

所以，总结一下，在任何包含名为AdmissionID的列的表中，我想删除所有AdmissionID等于指定值的记录。 我认为我可以很容易地做到这一点。 以下是我的存储过程：

ALTER PROCEDURE [Admin].[sp_RemoveByAdmissionID]

@admissionID int

AS
--Removes records from all tables relating the AdmissionID entered

DECLARE @loop INT
DECLARE @object sysname
DECLARE @cmdstring varchar(500)

IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL 
 BEGIN
  DROP TABLE #TEMP
 END

SELECT 
    TABLE_NAME
    ,ROW_NUMBER() OVER (ORDER BY TABLE_NAME) ROWID
INTO #TEMP
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE'

WHILE @loop <= (SELECT MAX(ROWID) FROM #TEMP)
  BEGIN
    SELECT @object = TABLE_NAME FROM #TEMP WHERE ROWID = @loop
        IF EXISTS (SELECT * FROM SYS.COLUMNS WHERE Name = 'AdmissionID' AND Object_ID = Object_ID(@object))
        BEGIN
            SET @cmdstring = 'DELETE FROM ' + @object + ' WHERE AdmissionID = ' + @admissionID
            Exec(@cmdstring)
        END    
  SET @loop += 1
  END

很不幸，执行此过程不会从任何地方删除记录。我怀疑问题可能与在构建cmdstring字符串的行中未将我的参数括在引号中有关，但即使尝试减轻此问题似乎也没有起作用。

您对我做错了什么有什么建议，或者是否有更简单的方法来实现这一点？我宁愿不使用PK-FK关系上的级联删除。

更新 根据Luv的建议，我发现他的查询确实返回了一个非常好的列表，其中包含我想要使用的精确命令。我以为从那里开始很容易，但我仍然无法让这个工作。我正在尝试的新SP如下：

ALTER PROCEDURE TEST

@admissionID VARCHAR(10)

AS

DECLARE @loop INT
DECLARE @cmd VARCHAR(500)


IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL 
BEGIN
DROP TABLE #TEMP
END

SELECT 'DELETE FROM '+ C.TABLE_NAME +' WHERE AdmissionID = '+@admissionID AS cmd
,ROW_NUMBER() OVER (ORDER BY 'DELETE FROM '+ C.TABLE_NAME +' WHERE AdmissionID = '+@admissionID) ROWID
INTO #TEMP
FROM INFORMATION_SCHEMA.COLUMNS C
INNER JOIN INFORMATION_SCHEMA.TABLES T on C.TABLE_NAME=T.TABLE_NAME
WHERE C.COLUMN_NAME='AdmissionID'
AND T.TABLE_TYPE='BASE TABLE'

WHILE @loop <= (SELECT MAX(ROWID) FROM #TEMP)
    BEGIN
SELECT @cmd = cmd 
FROM #TEMP
WHERE ROWID = @loop

EXEC(@cmd)      
SET @loop += 1
END

- Philip Stratford

---

你尝试过使用PRINT @cmdstring而不是EXEC来检查语句是否正确吗？ - Martin Smith

Answer 1

将下面的查询的结果复制并运行该查询。

declare @yourvalue varchar
set @yourvalue=''
select 'DELETE FROM '+ C.TABLE_NAME +' WHERE AdmissionID = '+@yourvalue 
from INFORMATION_SCHEMA.COLUMNS C
INNER JOIN INFORMATION_SCHEMA.TABLES T on C.TABLE_NAME=T.TABLE_NAME
where C.COLUMN_NAME='AdmissionID'
and T.TABLE_TYPE='BASE TABLE'

您甚至可以创建一个SP并进行动态执行

- Prahalad Gaggar

---

这是适合我的答案，如果你将列名(AdmissionID)更改为一个变量，比如 @ColumnName，它将一直可用。 - Furkan Ekinci

为什么？如果我将列名作为参数传递给过程，它应该适用于所有表中的所有列。这样做有问题吗？ - Furkan Ekinci

那个查询非常好用，谢谢你，但我仍然无法在SP中让它工作。我已经更新了我的原始问题，并尝试了一些东西 - 你能帮我看看吗？谢谢！ - Philip Stratford

我只是缺少了一行代码 SET @loop = 1。加上这行代码后问题得到解决。 - Philip Stratford