Escape single quote and wildcard in TSQL LIKE

Question

We are making an update to the way we structure the ProjectID field in our database. Currently there are values such as PD80400 which identifies a specific project.

There are likely stored procedures which use the PDXXXXX format in the Where clause such as ProjectID NOT LIKE 'PD%'

I need to search our database for any Procedures / Views / Tables / Function / etc. which contains a reference to PD% Currently I am using the following script but am having trouble capturing my test procedure which contains Where ProjectID NOT LIKE 'PD%' without searching for %PD%.... whcih is returning far too many undesired results such as anything with the words Update, Updated, etc.

My script:

SELECT DISTINCT a.[name], b.[text], CASE WHEN a.type IN ('FN', 'TF') THEN 'Function' WHEN a.type = 'P' THEN 'Stored Procedure' WHEN a.type = 'V' THEN 'View'  ELSE 'Unknown' END AS 'ObjectType', a.type
FROM sysobjects a
INNER JOIN syscomments b on a.id = b.id
WHERE b.[text] LIKE '%PD%' AND a.name = 'AAAAAAAAAAAAA_TBG_MM_TestProcedure_PDSearch'--AND b.[text] NOT LIKE 'update%' AND b.[text] NOT LIKE 'EmpD%' AND b.[text] NOT LIKE 'updated' AND a.name NOT LIKE 'Z_OLD%' AND a.name NOT LIKE 'ZOLD%'
ORDER BY ObjectType

How should I format my LIKE statement in order to capture examples like I listed above without all the extra results?

Answer 1

You can escape the % wildcard by specifying an escape character, and to include a single quote use two single quotes like so:

select 
    a.[name]
  , b.[text]
  , case when a.type in ('fn', 'tf') then 'Function' 
         when a.type = 'P' then 'Stored Procedure' 
         when a.type = 'V' then 'View'  
         else 'Unknown' end as 'ObjectType', a.type
from sysobjects a
  inner join syscomments b on a.id = b.id
where b.[text] like '%''PD\%%' escape '\'
order by ObjectType

---

To test with two dummy procedures:

create procedure dbo.pd_search as 
select * from master..spt_values 
where number = 1
  and name not like 'PD%'
go
create procedure dbo.pd_search_other as 
select * from master..spt_values 
where number = 1
  and name <> 'PD'
go

rextester demo: http://rextester.com/KPC17170

returns:

+-----------+------------------------------------+------------------+------+
|   name    |                text                |    ObjectType    | type |
+-----------+------------------------------------+------------------+------+
| pd_search | create procedure dbo.pd_search as  | Stored Procedure | P    |
|           | select * from master..spt_values   |                  |      |
|           | where number = 1                   |                  |      |
|           |   and name not like 'PD%'          |                  |      |
+-----------+------------------------------------+------------------+------+