CODEWITHSUNDEEP
sqlsql-servert-sql
MJJM
MJJM

Reputation: 345

SQL query with sub query with IN or LIKE

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;

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?

Upvotes: 0

Views: 69

Answers (2)

AXMIM
AXMIM

Reputation: 2472

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.

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269543

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.

Upvotes: 1

Related Questions