带有IN或LIKE的子查询的SQL查询

I have a table with 50 rows in which are replication articles. I need to pick up these names and query sys.sql_modules to see if the article name exists anywhere inside the definition (proc text). I've tried the below based on a similar suggestion from another thread on here but it reports all rows back which I'm not expecting;

我有一个包含50行的表,其中包含复制文章。我需要获取这些名称并查询sys.sql_modules以查看文章名称是否存在于定义(proc文本)中的任何位置。我已经尝试了下面基于来自另一个线程的类似建议,但它报告了所有我不期待的行;

SELECT Article,
       CASE WHEN exists (SELECT OBJECT_NAME(object_id) AS name,
                                definition
                         FROM sys.sql_modules
                         WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
                           AND definition LIKE '%database%') 
              THEN 'YES else'
           ELSE article
       END
FROM dbo.Article

Maybe I need to try querying it the other way around? So populate a temp table with the syntax from all stored procedures and then query the article name against that?

也许我需要尝试反过来查询它?那么使用所有存储过程的语法填充临时表,然后查询文章名称?

2 个解决方案

#1

You have no relationship between the article and the module. I am guessing that you want to find Article in the definition, which suggests logic like this:

您与文章和模块之间没有任何关系。我猜你想要在定义中找到文章,这表明这样的逻辑:

SELECT a.Article, m.definition
FROM dbo.Article a JOIN
     sys.sql_modules m
     ON OBJECTPROPERTY(m.object_id, 'IsProcedure') = 1 AND
        m.definition LIKE '%database%' AND
        m.definition LIKE '%' + a.Article_Name + '%'

I don't know if the condition on "database" is needed, but it is part of your original question.

我不知道是否需要“数据库”的条件,但这是原始问题的一部分。

#2

Your subquery isnt' linked to the query, I assume that is what you mean by "exist anywhere".

你的子查询没有与查询相关联,我认为你的意思是“存在于任何地方”。

In the code bellow, it search only the article name in object definition. CHARINDEX is used instead of LIKE to search if article exists within the definition.

在下面的代码中,它仅搜索对象定义中的文章名称。使用CHARINDEX而不是LIKE来搜索定义中是否存在文章。

SELECT Article,
       CASE WHEN exists (SELECT OBJECT_NAME(object_id) AS name,
                                definition
                         FROM sys.sql_modules
                         WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
                           AND  CHARINDEX(article, definition) > 0) 
              THEN 'YES else'
           ELSE article
       END
FROM dbo.Article

#1