How to get all columns with empty value in table

Question

How to get all columns with empty value in table

yaya1899

SSC Eights!

Points: 937
More actions
January 22, 2009 at 8:17 pm

#383792

Hi,
I would like to get all records with any column has empty value in a table. I understand that by using the following SQL will return the result.
SELECT * FROM table WHERE col1 = '' OR col2 = ''
But my problem is, the table has about 100 fields. Is there a faster way to do this instead of specifying all the fieldname?
If there is, can it be applied on table variable?
Any advice would be much appreciated, thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

here's a cursor based solution that finds all char/varchar columns for a given table, then queries each for empty /nulls, aqnd provides the results, along with the query to find the data.

[font="Courier New"]

ALTER PROCEDURE EMPTYSEARCH

-- EXEC EMPTYSEARCH 'WorldAirports2006'

@WHICHTABLE VARCHAR(50)

AS

SET NOCOUNT ON

DECLARE @SQL VARCHAR(500),

@TABLENAME VARCHAR(60),

@COLUMNNAME VARCHAR(60)

CREATE TABLE #RESULTS(TBLNAME VARCHAR(60),COLNAME VARCHAR(60),SQL VARCHAR(600))

SELECT

SYSOBJECTS.NAME AS TBLNAME,

SYSCOLUMNS.NAME AS COLNAME,

TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE

INTO #FKFINDER

FROM SYSOBJECTS

INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID

WHERE SYSOBJECTS.XTYPE='U'

AND SYSOBJECTS.NAME = @WHICHTABLE

AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')

ORDER BY TBLNAME,COLNAME

DECLARE C1 CURSOR FOR

SELECT TBLNAME,COLNAME FROM #FKFINDER ORDER BY TBLNAME,COLNAME

OPEN C1

FETCH NEXT FROM C1 INTO @TABLENAME,@COLUMNNAME

WHILE @@FETCH_STATUS <> -1

BEGIN

--SET @SQL = 'SELECT ''' + @TABLENAME + ''' AS TABLENAME,''' + @COLUMNNAME + ''' AS COLUMNNAME,* FROM ' + @TABLENAME + ' WHERE ' + @COLUMNNAME + ' LIKE ''%' + @WHICHTABLE + '%'''

SET @SQL = 'IF EXISTS(SELECT * FROM [' + @TABLENAME + '] WHERE ISNULL([' + @COLUMNNAME + '],'''') ='''') INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL) VALUES(''' + @TABLENAME + ''',''' + @COLUMNNAME + ''','' SELECT * FROM [' + @TABLENAME + '] WHERE ISNULL([' + @COLUMNNAME + '],'''''''') ='''''''''') ;'

PRINT @SQL

EXEC (@SQL)

FETCH NEXT FROM C1 INTO @TABLENAME,@COLUMNNAME

END

CLOSE C1

DEALLOCATE C1

SELECT * FROM #RESULTS

[/font]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 2

Using a cursor in 2k5 when VARCHAR(MAX) is available? Lowell? :hehe:

--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)

Lowell SSC Guru Points: 323518 More actions · Answer 3

ok...i'll bite...how do you find each column that hass null/empty string as a report without a cursor?

at least in this case, i only saw dynamic sql and a cursor....i can't envision a single statement to do this, especially with the @WhichTable requirement.

is it possible, Jeff or are you just pork chopping me for no reason?

I admit i was lazy and tweaked a previous example, but still.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 4

wait, i think i get it....still use a loop or FOR XML to construct the complete set of statements, and run the massive varchar(max) as a batch, instead of line by line...i'm properly chastised now.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 5

I agree that dynamic SQL is the way to go here. You could concatenate the same dynamic SQL you have in your cursor using the ol' SELECT @SQL = ISNULL(@SQL+CHAR(10),'') + bigstringyouused FROM sametableyoudid where @SQL is a VARCHAR(MAX). Or, you could do it in a single INSERT with a bazillion OR's, again formed in a similar fashion.

Heh... and no... you're one of the good guys... no pork chops for the sake of slinging them.

Despite my feelings about them, the cursor isn't really a bad idea for something like this. Compared to the rest of the code, the performance impact of the cursor will be near nil. I just worry about some newbie seeing that a good guy used a cursor and that'll give 'em reason to use them anytime they can't think of something else.

--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)

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 6

Lowell (1/22/2009)
wait, i think i get it....still use a loop or FOR XML to construct the complete set of statements, and run the massive varchar(max) as a batch, instead of line by line...i'm properly chastised now.

Sorry about my other post. Didn't see this one while I was typing. And, yes Sir, you've got it! 🙂 Except that you don't need to use a loop.

--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)

yaya1899 SSC Eights! Points: 937 More actions · Answer 7

Thanks Lowell, I managed to get an idea based on your sample.

😀