For all the power of SQL Server, some things a still missing. In Microsoft Word or Notepad++, when you search for a word in a document, you get the count of appearances, too. But in SQL, you can search for a string in a VARCHAR field, but the result only appears once. What I really needed was to count the occurrences of a string within a column. In the example below, we needed to change the legacy SQL 2005 style RASIERROR 50000 syntax into the modern RAISERROR(”,16,1) syntax. The business needed a count of the fixes, by object. Of course, I hit up the web and found 90% of what I needed on SQL Mag: SQL Server Pro: Counting Instances of a Word in a Record

I needed to know a bit more information, including object type and object name.

SELECT  object_name(sm.OBJECT_ID) as NAME
	   , so.[TYPE]
	   , SUM((LEN(sm.[definition] ) - LEN(REPLACE(sm.[definition] , 'RAISERROR 50000', '')))/LEN('RAISERROR 50000')) as FOUND
FROM    sys.sql_modules as sm
	   inner join sys.objects as so
		  on sm.OBJECT_ID = so.OBJECT_ID
where   sm.[definition] like '%RAISERROR 50000%'
group by sm.OBJECT_ID, so.[type]
order by object_name(sm.OBJECT_ID)

The results look like this:

Which is the count of the legacy RAISERROR statements within create statements.