CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2008-r2
CIPHER
CIPHER

Reputation: 237

Find blank cells in entire table without specifying all column names in OR condition

There are 190 columns in my table and I trying to list out empty or blank cells. Already i know to check this condition in where clause by using OR, but for no. of 190 columns it seems very long work....So I Tried following query in order to list out null values but when i alter the query according to my criteria by changing in the place of 'IS NULL' as '' to get empty cells it doesn't works

DECLARE @tb NVARCHAR(255) = N'dbo.[Sales_Five_Years]';

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

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

EXEC sp_executesql @sql;

Please guide me, Thanks in advance

Upvotes: 1

Views: 1766

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93754

Try this

SELECT @sql += N' OR convert(varchar(50),' + Quotename(name) + ')='''''
FROM   sys.columns
WHERE  [object_id] = Object_id(@tb);

check this example i didn't get any error.

CREATE TABLE pr1
  (
     col  VARCHAR(50),
     col1 VARCHAR(50)
  )

INSERT INTO pr1
SELECT '',NULL
UNION
SELECT NULL,'a'
UNION
SELECT 'b','a'


SELECT @sql += N' OR convert(varchar(50),' + Quotename(name) + ')='''''
FROM   sys.columns
WHERE  [object_id] = Object_id(@tb);

PRINT @sql

EXEC Sp_executesql
  @sql;

Upvotes: 1

Related Questions