Finding error messages intended for the user application in an immense amount of TSQL code

itnifl used Ask the Experts™
In an effort to streamline and systematize error message that are presented to the user from the application system we are developing/sustaining, I have made a complete list of error message and where they are found in the code base.

Except for error messages that origin from views and stored procedures in the MSSQL database that is the backend for this system.

When I searched for error messages in the application code base, I used regular expressions to look for hardcoded error messages in the source code. I also manually looked through all .resx files for error messages. But I am not sure of what is the best approach when searching for error messages in views and stored procedures.

The amount of views and stored procedures in the database backend is huge, and it will take an immense amount of time to read through it all manually. I also don't trust that I will catch all error messages sufficiently by manually reading all the code.

So, are there any good ideas on how to approach this task of finding error messages in views and stored procedures in an MSSQL database in a quick as possible and efficient way?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Do they have any format / construct as a message ?

Otherwise you would be searching for strings and if error messages are unstructured, will be hard to differentiate....

Maybe a couple of examples would help....

You could export SP's Functions, Views etc to disk file and use a similar approach with your resx files....


I exported the whole database into one big sql file using ReadyRoll Pro. I ended up with a scriptfile that was 99 192 lines long. Started out by searching for text like 'RAISERROR', 'check' and 'error' in the scriptfile using Notepad++. It became clear that some stored procedures where being used for error handeling. I used the name of these and searched them up by using a text search for further information. This way I found 22 places where error messages were being generated, but I bet there could be far more.
Topic Advisor, Page Editor
Distinguished Expert 2018
Good start !!

It can be a royal pain.... But years of T-SQL routines / procedures will result in exactly what you are finding. Unfortunately.

One software house I was in charge of, we had formalised, structured messaging. We ran a survey amongst a several thousand "key users" about the messaging the system produced.

Surprisingly, even with strict controls in place, the most frustrating of messages (all round) were the simple innocuous messages like "not found" which of course didnt comply with our strict standards. The argument being that the message was so unlikely to occur.... Yeah right.

The moral of the story ?

It is a very difficult task in a largely unstructured environment like T-SQL.

The bigger question is having a full code review and deciding what to do (if anything).

In that regard, using SSMS, it can be easier to right click on the database, go into TASKS, then GENERATE SCRIPTS, and choose the objects to script - starting with Stored Procedures / Functions - because they *should be* the more obvious....

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial