sql-server - 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+''')'

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 语句的构造放入单独的操作中。