How to searh for a string in all stored procedures within a DB

We recently had to migrate from PCT commissioning to CCG. Because of this I had to search all stored procedures within our data warehouse to check where the commissioner was being set. As our data warehouse is very large with hundreds of stored procedures this was an incredible feat to do manually. The SQL script below really helped me.

SELECT
  obj.Name SPName, sc.TEXT SPText
FROM
  sys.syscomments sc
  INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID
WHERE
  sc.TEXT LIKE '%' + '<The string you want to search for>' + '%'
  AND TYPE = 'P'
GO

Just replace the <The string you want to search for> with the string you want to search. See example below


SELECT
  obj.Name SPName, sc.TEXT SPText
FROM
  sys.syscomments sc
  INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID
WHERE
  sc.TEXT LIKE '%' + 'PrimaryCareTrust' + '%'
  AND TYPE = 'P'
GO