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.