sql-server - SQL Server sp_msforeachtable用法，仅选择那些满足某些条件的表

我正在尝试编写此查询来查找具有特定列和特定值的所有表。到目前为止，这是我所做的-

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

最佳答案

您知道sp_MSforeachtable如何未记录在案，并且可能随时消失/被修改吗？

好吧，如果您乐于忽略它，它还有另一个名为@whereand的参数，该参数附加到用于查找表的内部查询的WHERE子句中(应该以AND开头)。

您还必须知道，别名是o的别名sysobjects，而别名是syso的别名sys.all_objects。

使用此知识，您可以将@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语句的构造移到了单独的操作中。

关于sql-server - SQL Server sp_msforeachtable用法，仅选择那些满足某些条件的表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/9679997/