find which field and table blank or null field

Question

Post reply

find which field and table blank or null field

Bruin

SSCrazy

Points: 2058
More actions
November 18, 2021 at 1:03 am

#3954255
I'm trying to turn this query into something that is more dynamic. I would like it to be able to grab a set of tables(example like sales%), and be able to tell me which table and field meets my criteria. then dump that record into some sort of generic error table.
Thanks.
```
DECLARE @tb nvarchar(512) = N'dbo.[salescube_export]';

DECLARE @sql nvarchar(max) = N'SELECT * FROM ' + @tb
    + N' WHERE 1 = 0';

SELECT @sql += N' OR ' + QUOTENAME(name) + N' IS NULL'
    FROM sys.columns 
    WHERE [object_id] = OBJECT_ID(@tb)
    AND is_nullable = 1;

EXEC sys.sp_executesql @sql;
```
November 22, 2021 at 12:25 pm

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Bruin SSCrazy Points: 2058 More actions · Answer 1

I also don't think it's checking for an Empty value in the fields.

thx.

Mike01 SSChampion Points: 11395 More actions · Answer 2

I think you want to check the entre table for fields that are null or have an empty string. You can try something like this

DECLARE @tb nvarchar(512) = N'dbo.[salescube_export]';
DECLARE @sql nvarchar(max) = N'SELECT * FROM ' + @tb
+ N' WHERE 1 = 0';
SELECT @sql += N' OR IsNull(cast(' + QUOTENAME(name) + N' as varchar(max)),'' '') = '' '''
FROM sys.columns
WHERE [object_id] = OBJECT_ID(@tb)
AND is_nullable = 1;
EXEC sys.sp_executesql @sql;

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Bruin SSCrazy Points: 2058 More actions · Answer 3

Works great, any way to dump the field data it finds to a generic error_log table, and be able to scan multiple tables at once.

Thx.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

Sorry. Post removed. I looked at the code and read it incorrectly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)