SQL Server sp_msforeachtable使用情况以仅选择那些满足某些条件的表
SQL Server sp_msforeachtable使用情况以仅选择那些满足某些条件的表
提问于 2012-03-13 15:34:54
我正在尝试编写这个查询来查找具有特定列和特定值的所有表。这是我到目前为止所做的-
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+''')'回答 2
Stack Overflow用户
回答已采纳
发布于 2012-03-13 15:57:35
你知道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语句的构造移到了单独的操作中。
Stack Overflow用户
发布于 2012-03-13 16:06:23
我猜你得到了某种错误，可能是Invalid column name '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
'页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/9679997
复制相关文章
相似问题
腾讯云开发者
