SQL Server sp_msforeachtable 用法仅选择满足某些条件的那些表 [英] SQL Server sp_msforeachtable usage to select only those tables which meet some condition

问题描述

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

I am trying to write this query to find all tables with specific column with some specific value. This is what I've done so far -

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' 的行.

I hope my intensions are clear, I just want to select only those tables where the column EMP_CODE is present and in those tables I want to select those rows where EMP_CODE='HO081'.

编辑 -

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

Now it stands like this. But I'm not able to replace @EMPCODE variable in the query.

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 是如何未记录的，并且可能随时消失/被修改吗?

You know how sp_MSforeachtable is undocumented, and may go away at any time/be modified?

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

Well, if you're happy to ignore that, it has another parameter called @whereand, which is appended to the WHERE clause of the internal query that is being used to find the tables (and should start with an AND).

您还必须知道有一个别名，o 对应于 sysobjects，第二个别名 syso 对应于 sys.all_objects.

You also have to know that there's an alias, o against sysobjects, and a second alias syso against sys.all_objects.

利用这些知识，您可以将 @whereand 参数制作为:

Using this knowledge, you might craft your @whereand parameter as:

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(*) 条件，因为我看不到它增加了什么值.

You can now also simplify your command1, since you know it will only be run against tables containing an EMP_CODE column. I'd probably take out the COUNT(*) condition also, since I don't see what value it's adding.

根据您进一步的工作进行了更新，并针对一张表进行了测试:

Updated based on your further work, and tested against one table:

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，因为您不想替换那里的值).

(I've reverted the @whereand to query for EMP_CODE, since you don't want to replace the value there).

问题在于，您可以将参数传递给存储过程或文字，但是您不能在它们之间执行计算/组合操作 - 所以我移动了将 sql 语句构造成一个单独的动作.

The issue is that, you can pass parameters to a stored procedure, or literals, but you can't perform calculations/combining actions between them - so I moved the construction of the sql statement out into a separate action.

这篇关于SQL Server sp_msforeachtable 用法仅选择满足某些条件的那些表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！