Find Null values in Non-nullable rows in SQL

Looking for a query that will scan a table schema, find all fields that are not nullable and return all rows in that table where one or more these fields are in fact, null.
branudaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Mike EghtebasDatabase and Application DeveloperCommented:
... not nullable ... are in fact, null  

How can it be not nullable yet null?
Jim HornMicrosoft SQL Server Data DudeCommented:
Guessing the only way that is possible is to add a NOT NULL constraint with NOCHECK after the NULL values were added.

>Looking for a query that will scan a table schema, find all fields that are not nullable
SELECT t.name as table_name, c.name as column_name
FROM sys.tables t
	JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.is_nullable = 0
ORDER BY t.name, c.name

Open in new window

>return all rows in that table where one or more these fields are in fact, null.
Have to think about that one..
Mike EghtebasDatabase and Application DeveloperCommented:
not nullable to me seemed the not null constraint is already in place.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

branudaAuthor Commented:
unfortunately the UI pushes null values into these fields and errors occur in the UI
Jim HornMicrosoft SQL Server Data DudeCommented:
>unfortunately the UI pushes null values into these fields and errors occur in the UI
Can you verify whether those rows were accepted by the database?  The error would imply that they are not.  Guessing there's a UI error log somewhere that captures these so the T-SQL can be read?
branudaAuthor Commented:
those rows were accepted by the database
branudaAuthor Commented:
and the error log just specifies the table, not the column
Jim HornMicrosoft SQL Server Data DudeCommented:
>and the error log just specifies the table, not the column
In that case I recommend eyeballing the table and manually figuring it out, as it would be a hell of a dynamic SQL statement to take my above T-SQL of tables and columns, and loop through all tables with a WHERE clause that test all columns for NULL values.
branudaAuthor Commented:
only one table but yes, that's what we have been doing
dameyCommented:
I would write a "batch" sql to create all the select statements then run them, normally in SSMS I set the results to Text the save them as a .sql file, open the file in SSMS then execute to find what I'm looking for. If your using grid view you can just select the column and copy paste.

For what your looking for (null values that should of never been allowed in) I would use this on your db to create the selects, or you can modify this to insert all the values into a "BadDataTable" or a delete etc.

Have fun!

SELECT  t.TABLE_NAME, c.COLUMN_NAME,
 'SELECT * FROM ' + t.TABLE_NAME + ' c ' +
 ' WHERE c.' + c.COLUMN_NAME + ' IS NULL ' [BadNullData]
FROM information_schema.tables t
      INNER JOIN Information_schema.Columns c ON t.TABLE_NAME = c.TABLE_NAME
WHERE c.is_nullable = 'NO' AND t.TABLE_TYPE = 'BASE TABLE'
ORDER BY t.TABLE_NAME, c.COLUMN_NAME

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
Microsoft SQL Server

From novice to tech pro — start learning today.