We help IT Professionals succeed at work.

Find and list column names that are NULL or blank

David C
David C asked
on
339 Views
Last Modified: 2014-05-16
Hi Experts, I am looking for ideas on a query that will allow me to find the names of columns that are blank or NULL and the id is any given number.
Comment
Watch Question

GanapathiFacets Developer

Commented:
If i underdtand correctly, you want to know the columns name from a particular table where the column has NULL/Blank in all the rows. Am I right?
David CIT Projects Manager

Author

Commented:
No, in any row. For example if I have a student and their record does not have a phone number and email but has an address I want the query to return Phone Number and Email.
GanapathiFacets Developer

Commented:
Try this.

CREATE TABLE STUDENT(SID INT, SNAME VARCHAR(30),EMAIL VARCHAR(50) NULL, PHONE VARCHAR(10) NULL)

INSERT INTO STUDENT SELECT 1,'PETER','peter@gmail.com','9167454542'
INSERT INTO STUDENT SELECT 2,'GEORGE','geo@gmail.com',NULL
INSERT INTO STUDENT SELECT 3,'LEO',NULL,'9167454542'
INSERT INTO STUDENT SELECT 4,'HARRY',NULL,''

SELECT 'StudentName' FROM STUDENT WHERE SID = 4 AND ISNULL(RTRIM(SNAME),'') = '' UNION ALL
SELECT 'Email' FROM STUDENT WHERE SID = 4 AND ISNULL(RTRIM(EMAIL),'') = '' UNION ALL
SELECT 'Phone' FROM STUDENT WHERE SID = 4 AND ISNULL(RTRIM(PHONE),'') = ''

Open in new window

David CIT Projects Manager

Author

Commented:
Thanks, this works great however the columns will not be hardcoded since I have quite a few. I need a query that does it dynamically. I had got this far

declare @col varchar(255), @cmd varchar(max)

DECLARE getinfo cursor for
SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
WHERE t.Name = 'STUDENT'

OPEN getinfo

FETCH NEXT FROM getinfo into @col

declare @ypid varchar(max)

WHILE @@FETCH_STATUS = 0
BEGIN

    SELECT @cmd = 'IF NOT EXISTS (SELECT * FROM STUDENT WHERE [' + @col + '] IS NOT NULL and SID=4) BEGIN print ''' + @col + ''' end'
	EXEC(@cmd)

    FETCH NEXT FROM getinfo into @col
END

CLOSE getinfo
DEALLOCATE getinfo

Open in new window


but the issue is I cannot declare a variable for SID and I cannot check if the column is <> '' because the apostrophes are breaking the synax.

The idea query is

SELECT @cmd = 'IF NOT EXISTS (SELECT * FROM STUDENT WHERE [' + @col + '] IS NOT NULL or [' + @col + '] <>'' ) and SID=@sid) BEGIN print ''' + @col + ''' end'

Open in new window


but I cannot seem to get it working
GanapathiFacets Developer

Commented:
Okay. If without declaring a variable, then you will have to hard code the SID or it will traverse all the records in the table.
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
E.g.

DECLARE @select_statement NVARCHAR(MAX);

WITH    user_tables_with_nullable_columns
          AS ( SELECT   T.object_id ,
                        QUOTENAME(S.name) + '.' + QUOTENAME(T.name) AS object_name ,
                        QUOTENAME(C.name) AS column_name
               FROM     sys.schemas S
                        INNER JOIN sys.tables T ON S.schema_id = T.schema_id
                        INNER JOIN sys.columns C ON T.object_id = C.object_id
               WHERE    T.type_desc = 'USER_TABLE'
                        AND C.is_nullable = 1
             ),
        column_selects
          AS ( SELECT   object_id ,
                        object_name ,
                        'SELECT ''' + object_name + ''' AS object_name, ''' + column_name + ''' AS column_name FROM ' + object_name + ' WHERE ' + column_name
                        + ' IS NULL' AS select_statement
               FROM     user_tables_with_nullable_columns
             )
    SELECT  @select_statement = 'SELECT Q.object_name, Q.column_name, COUNT(*) as nulls_in_column FROM ( ' + 
                STUFF(( SELECT ' UNION ALL ' + select_statement
                FROM   column_selects
                FOR XML PATH('')
        ), 1, 11, '')
            + ' ) Q GROUP BY Q.object_name, Q.column_name ;';

EXECUTE(@select_statement );

Open in new window

PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
rather than run one query for every field, why not run one query with one case expression per field? This assume [SID] is the column that identifies a row in the final output (and that bit is hardcoded) all other fields are dynamically handled.
    CREATE TABLE STUDENT(SID INT, SNAME VARCHAR(30),EMAIL VARCHAR(50) NULL, PHONE VARCHAR(10) NULL)
    
    INSERT INTO STUDENT SELECT 1,'PETER','peter@gmail.com','9167454542'
    INSERT INTO STUDENT SELECT 2,'GEORGE','geo@gmail.com',NULL
    INSERT INTO STUDENT SELECT 3,'LEO',NULL,'9167454542'
    INSERT INTO STUDENT SELECT 4,'HARRY',NULL,''

**Query 1**:

    DECLARE @sql AS nvarchar(MAX)
    DECLARE @flds AS nvarchar(MAX)
    
    
    SET @sql = ''
    
    
    + '  SELECT'
    + '         ''STUDENT'' as tname'
    + '       , sid AS row_identity'
    + '       , COLUMN_NAME'
    + '       , ISNULL'
    + '  FROM STUDENT'
    + '  CROSS apply ('
    + '              VALUES'
    
    SELECT
        @flds = stuff((
                        SELECT ', (''' + c.name + ''', CASE WHEN ' + c.name + ' IS NULL THEN 1 ELSE 0 END)'
                        FROM sys.columns AS c
                        WHERE t.Object_ID = c.Object_ID
                        FOR XML PATH ('')
                       ), 1, 1, '')
    FROM sys.tables t
    WHERE t.name = 'STUDENT'
    
    SET @sql = @sql
                + @flds
                + '              ) ca1 (COLUMN_NAME, ISNULL)'
                + '  WHERE ca1.ISNULL = 1'
    
    
    
    --SELECT @sql
    
    EXEC(@sql)
    	
    	
    

**[Results][2]**:
    
    |   TNAME | ROW_IDENTITY | COLUMN_NAME | ISNULL |
    |---------|--------------|-------------|--------|
    | STUDENT |            2 |       PHONE |      1 |
    | STUDENT |            3 |       EMAIL |      1 |
    | STUDENT |            4 |       EMAIL |      1 |



  [1]: http://sqlfiddle.com/#!3/90136d/2

Open in new window

PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
BTW: the generated query looks like the following. It uses a technique employing VALUES to transform one row into many rows - here one row per field
SELECT
     'STUDENT' AS tname
    , sid AS row_identity
    , COLUMN_NAME
    , ISNULL
FROM STUDENT
CROSS APPLY (
    VALUES
          ( 'SID'    , CASE WHEN SID IS NULL THEN 1 ELSE 0 END    )
        , ( 'SNAME'  , CASE WHEN SNAME IS NULL THEN 1 ELSE 0 END  )
        , ( 'EMAIL'  , CASE WHEN EMAIL IS NULL THEN 1 ELSE 0 END  )
        , ( 'PHONE' , CASE WHEN PHONE IS NULL THEN 1 ELSE 0 END   )
    ) ca1(COLUMN_NAME, ISNULL)
WHERE ca1.ISNULL = 1

Open in new window

David CIT Projects Manager

Author

Commented:
Thank you all so much for your feedback. I have managed to get it working using,

declare @col varchar(255), @cmd varchar(max)

DECLARE getinfo cursor for
SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
WHERE t.Name = 'STUDENT'

OPEN getinfo

FETCH NEXT FROM getinfo into @col

WHILE @@FETCH_STATUS = 0

BEGIN
    SELECT @cmd = 'IF EXISTS (SELECT * FROM STUDENT WHERE ([' + @col + '] IS NULL or [' + @col + '] = '''' ) and SID=4) BEGIN print ''' + @col + ''' end'
	--SELECT @cmd
	EXEC(@cmd)

    FETCH NEXT FROM getinfo into @col
END

CLOSE getinfo
DEALLOCATE getinfo

Open in new window


Is there anything I can do to change SID=4 to use a parameter instead? This way I can put this as a stored procedure.
CERTIFIED EXPERT
Top Expert 2012

Commented:
I am sure there has to be a better way of writing that code, but to answer your question: Is there anything I can do to change SID=4 to use a parameter instead? Something like this should do it:
CREATE PROCEDURE usp_YourStoredProcedureNameGoesHere
		@SID integer

AS

declare @col varchar(255), @cmd varchar(max)

DECLARE getinfo cursor for
SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
WHERE t.Name = 'STUDENT'

OPEN getinfo

FETCH NEXT FROM getinfo into @col

WHILE @@FETCH_STATUS = 0

BEGIN
    SELECT @cmd = 'IF EXISTS (SELECT * FROM STUDENT WHERE ([' + @col + '] IS NULL or [' + @col + '] = '''' ) and SID=' + CAST(@SID AS varchar(10)) + ' BEGIN print ''' + @col + ''' end'
	--SELECT @cmd
	EXEC(@cmd)

    FETCH NEXT FROM getinfo into @col
END

CLOSE getinfo
DEALLOCATE getinfo

Open in new window

David CIT Projects Manager

Author

Commented:
Thank Anthony, I am getting the error
Incorrect syntax near the keyword 'BEGIN'.

Open in new window


I am happy to take suggestions as I want the best and most efficient code. Please let me know yours and I'll be happy to test it out
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
@takwirirar, I think you should use an entire dynamic approach and avoid the cursor. E.g. a shorter version of my first version:

DECLARE @test_statement NVARCHAR(MAX) = N'SELECT ''@column_name'' AS column_name, COUNT(*) FROM [dbo].[STUDENT] WHERE @column_name IS NULL';
DECLARE @result_statement NVARCHAR(MAX);

WITH    user_tables_with_nullable_columns
          AS ( SELECT   QUOTENAME(C.name) AS column_name
               FROM     sys.schemas S
                        INNER JOIN sys.tables T ON S.schema_id = T.schema_id
                        INNER JOIN sys.columns C ON T.object_id = C.object_id
               WHERE    C.is_nullable = 1
			AND S.name = 'dbo'
                        AND T.Name = 'STUDENT'
             ),
        column_selects
          AS ( SELECT   REPLACE(@test_statement, '@column_name', column_name) AS select_statement
               FROM     user_tables_with_nullable_columns
             )
    SELECT  @result_statement = STUFF(
					( 
						SELECT  ' UNION ALL ' + select_statement
						FROM    column_selects
						FOR XML PATH('')
					)
					, 1, 11, '');             

EXECUTE  ( @result_statement );

Open in new window

David CIT Projects Manager

Author

Commented:
Hi ste5an I have changed the test statement to

DECLARE @test_statement NVARCHAR(MAX) = N'SELECT ''@column_name'' AS column_name, COUNT(*) FROM @object_name WHERE (@column_name IS NULL or @column_name='''') and SID=4';

Open in new window


This allows me to get columns that are NULL or blank.

The results I get show as below. How do I change the query to get only the columns that are greater than zero(these are the ones I want)
result setAlso how can I incorporate a parameter to get rid of SID=4
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
E.g.
DECLARE @test_statement NVARCHAR(MAX) = N'SELECT ''@column_name'' AS column_name, COUNT(*) FROM [dbo].[STUDENT] WHERE @column_name IS NULL HAVING COUNT(*) != 0 ';

Open in new window


By using a parameter??

DECLARE @test_statement NVARCHAR(MAX) = N'SELECT ''@column_name'' AS column_name, COUNT(*) FROM [dbo].[STUDENT] WHERE @column_name IS NULL AND SID = @SID HAVING COUNT(*) != 0 ';

-- the remainging code from above except the EXEC(variable).

EXECUTE sp_executesql @result_statement, N'@SID INT', @SID = 4;

Open in new window

David CIT Projects Manager

Author

Commented:
Thanks this is how I currently have it
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_executesql] 
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @test_statement NVARCHAR(MAX) = N'SELECT ''@column_name'' AS column_name, COUNT(*) FROM @object_name WHERE (@column_name IS NULL or @column_name='''') and SID=@SID HAVING COUNT(*) != 0';
DECLARE @result_statement NVARCHAR(MAX);

WITH    user_tables_with_nullable_columns
          AS ( SELECT   T.object_id ,
                        QUOTENAME(S.name) + '.' + QUOTENAME(T.name) AS object_name ,
                        QUOTENAME(C.name) AS column_name
               FROM     sys.schemas S
                        INNER JOIN sys.tables T ON S.schema_id = T.schema_id
                        INNER JOIN sys.columns C ON T.object_id = C.object_id
               WHERE    T.type_desc = 'USER_TABLE'
                        AND C.is_nullable = 1
                        AND T.Name = 'STUDENT'
             ),
        column_selects
          AS ( SELECT   REPLACE(REPLACE(@test_statement, '@object_name', object_name), '@column_name', column_name) AS select_statement
               FROM     user_tables_with_nullable_columns
             )
    SELECT  @result_statement = STUFF(
										( 
											SELECT  ' UNION ALL ' + select_statement
											FROM    column_selects
											FOR XML PATH('')
										)
										, 1, 11, '');             

EXECUTE  ( @result_statement );
END

Open in new window


When I run
EXECUTE sp_executesql @result_statement, N'@SID INT', @SID = 4;

Open in new window


It errors with

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@result_statement".

Open in new window

Senior Developer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
David CIT Projects Manager

Author

Commented:
Thanks for your help and patience. I am now getting

Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@object_name".
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@object_name".
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@object_name".
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@object_name".

Open in new window

CERTIFIED EXPERT
Top Expert 2012

Commented:
Thanks for your help and patience. I am now getting
Whose solution are you using?  The last one posted by ste5an does not include a variable called @object_name
David CIT Projects Manager

Author

Commented:
Thanks this works! Was getting a few other errors because of confusion with other stored procedures

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.