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

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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
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....
itniflAuthor Commented:
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.
Mark WillsTopic AdvisorCommented:
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....

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Regular Expressions

From novice to tech pro — start learning today.