SQL Server sp_msforeachtable用法：仅选择符合某些条件的表

Question

我正在尝试编写查询以查找具有特定列且具有某些特定值的所有表。这是我迄今为止所做的 -

EXEC sp_MSforeachtable 
@command1='
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=PARSENAME("?",2) AND TABLE_NAME=PARSENAME("?",1) AND COLUMN_NAME="EMP_CODE")
BEGIN
    IF (SELECT COUNT(*) FROM ? WHERE EMP_CODE="HO081")>0
    BEGIN
        SELECT * FROM ? WHERE EMP_CODE="HO081"
    END
END
'

我希望我的意图很清楚，我只想选择那些存在 EMP_CODE 列的表，然后在这些表中选取那些 EMP_CODE='HO081' 的行。

编辑 -

现在是这样的。但我无法在查询中替换 @EMPCODE 变量。

DECLARE @EMPCODE AS VARCHAR(20)
SET @EMPCODE='HO081'
EXEC sp_MSforeachtable 
@command1='
    DECLARE @COUNT AS INT
    SELECT @COUNT=COUNT(*) FROM ? WHERE EMP_CODE='''+@EMPCODE+'''
    IF @COUNT>0
    BEGIN
        PRINT PARSENAME("?",1)+'' => ''+CONVERT(VARCHAR,@COUNT)+'' ROW(S)''
        --PRINT ''DELETE FROM ''+PARSENAME("?",1)+'' WHERE EMP_CODE='''''+@EMPCODE+'''''''
    END
',@whereand='AND O.ID IN (SELECT OBJECT_ID FROM SYS.COLUMNS C WHERE C.NAME='''+@EMPCODE+''')'

- Soham Dasgupta

Answer 1

你知道 sp_MSforeachtable 是未经记录的，并且可能随时消失/被修改吗？

好吧，如果你愿意忽略这一点，它还有另一个名为 @whereand 的参数，它被追加到正在用于查找表的内部查询的 WHERE 子句中(并且应该以 AND 开头)。

你还要知道，对于 sysobjects 有一个别名 o，对于 sys.all_objects 有第二个别名 syso。

利用这些知识，你可以将你的 @whereand 参数制作成：

EXEC sp_MSforeachtable 
@command1='...',
@whereand='AND o.id in (select object_id from sys.columns c where c.name=''EMP_CODE'')'

现在您还可以简化command1，因为您知道它只会针对包含EMP_CODE列的表运行。我可能会删除COUNT(*)条件，因为我不认为它有什么价值。

---

基于您的进一步工作进行了更新，并已针对一个表进行了测试：

DECLARE @EMPCODE AS VARCHAR(20)
SET @EMPCODE='HO081'
declare @sql nvarchar(2000)
set @sql = '
    DECLARE @COUNT AS INT
    SELECT @COUNT=COUNT(*) FROM ? WHERE EMP_CODE='''+@EMPCODE+'''
    IF @COUNT>0
    BEGIN
        PRINT PARSENAME("?",1)+'' => ''+CONVERT(VARCHAR,@COUNT)+'' ROW(S)''
        --PRINT ''DELETE FROM ''+PARSENAME("?",1)+'' WHERE EMP_CODE='''''+@EMPCODE+'''''''
    END
'
EXEC sp_MSforeachtable 
@command1=@sql,@whereand='AND O.ID IN (SELECT OBJECT_ID FROM SYS.COLUMNS C WHERE C.NAME=''EMP_CODE'')'

(我已将@whereand还原为查询EMP_CODE，因为您不想在那里替换值)。

问题在于，您可以将参数传递给存储过程或使用文字字面量，但是您无法在它们之间执行计算/组合操作——因此，我将SQL语句的构建移动到了单独的操作中。

- Damien_The_Unbeliever

---

+1 表示向你展示了可以做什么，但同时含蓄地告诉你不要去做 ;) - cairnz

@Damien_The_Unbeliever 假设我写了 IF (SELECT COUNT(*) FROM ? WHERE EMP_CODE='''+@EMPCODE+''')>0，为什么会提示“附近有错误的语法”？我试图通过变量传递 EMP_CODE 的值。 - Soham Dasgupta

@SohamDasgupta - 在评论部分诊断这种问题有点棘手，因为我真的需要看到整个查询的现状，而这在评论中是行不通的。我会再次查询特定的代码片段 - 为什么要计算匹配行的数量，而不是简单地选择它们？空结果集通常很容易处理。 - Damien_The_Unbeliever

@Damien_The_Unbeliever 我在问题中发布了新的查询，请查看。 - Soham Dasgupta

@SohamDasgupta - 我在我的回答底部添加了一个更新的示例，并解释了为什么这是必要的。我想再次质疑你为什么在任何进一步操作之前执行COUNT - 运行SELECT（根据您的原始问题）或DELETE（根据您的更新）是完全可以的，它实际上不会影响任何行。首先执行COUNT是多余的，可能会导致查询需要更长时间才能执行。 - Damien_The_Unbeliever

@Damien_The_Unbeliever 我希望我有另一个赞的机会。非常感谢。 - Soham Dasgupta

Answer 2

我猜你遇到了某种错误，可能是无效的列名 'EMP_CODE'吗？

这是因为在检查列之前，代码已经被编译了。你可以像这样做。

EXEC sp_MSforeachtable 
@command1='
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=PARSENAME("?",2) AND TABLE_NAME=PARSENAME("?",1) AND COLUMN_NAME="EMP_CODE")
BEGIN
   EXEC(''
          IF (SELECT COUNT(*) FROM ? WHERE EMP_CODE="HO081")>0
          BEGIN
              SELECT * FROM ? WHERE EMP_CODE="HO081"
          END
        '')
END
'

- Mikael Eriksson