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.

Rudy

Rudy Rodarte is a SQL Server Database professional based in Austin, TX. Over his career, Rudy has worked with SSRS, SSIS, performance tuning troubleshooting. When away from Keyboard, Rudy goes to Spurs and Baylor Bear sporting events.

More Posts - Website - Twitter