lm_hamilton (10/7/2012)
Our clients use MS-SQL 2000 through 2008, and soon will have some using 2012.WinXP through Win7, very few on Vista. 32-bit & 64-bit.
The issue is an old application bug allowed data that was incorrect to be written. For example, NULL where it should not be NULL, and empty strings, '' or strings with a single space ' '. I'm on the support side, so I can't control how the program does its job, but the programmers don't have any capacity to give us a quick solution, so I am researching to see if there is a way to save time finding this weird data.
This is not an issue of database corruption because "DBCC CHECKDB (OWFIN) WITH NO_INFOMSGS, ALL_ERRORMSGS" returns no errors.
There are some instances where old data can cause unexpected and incorrect results on some reports and it is time consuming to track down which table(s) has the problem data.
Are you absolutely sure that fixing "some reports" won't break everything else?
Other than writing a script customized to each table in the database, is there a quick script that can check all tables in the database for NULL, '', and ' '?
I have tried googling for a script/how to along those lines and searching this site, but am not coming up with something that will work. Either I am not asking for the correct term, or don't have the right combination of words to find it, if such a thing exists.
I have built a lot of scripts that look for known data issues caused by old bugs or incorrect solutions to those bugs implemented by former techs. Some of these scenarios are unique enough that no catch-all script will work, but for scenarios where values that should not be written have been written, is there a quick and easy script to find them?
These tables have constraints for "Not Null", but the bug let NULL be written anyway. Is there an easy way to get a list where a "not Null" column has Null in it?
That's unlikely. If a column has a not null constraint, it won't allow nulls.
Thanks for any links, tips, suggestions, or scripts that will help with this.
~ Larry
A shotgun approach like this puts shivers down my spine. You have a legacy app which uses NULL, empty string and a single space, all of which mean different things and on which the app and the database probably rely for data integrity and functionality, and you want to change all of these values to support a few reports. I'd recommend you perform a great deal more testing before proceeding with this. Find out exactly why the reports are giving unexpected results - track down the tables and columns used by them and assess the impact on other processes of the changes which you would make to those tables and columns. If you're going to make any changes then do it in a test environment.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden