1

I have a table with following columns: F1, F2, ...F10 Some of these columns contain only NULLS, let's say they are F2 and F7. How can I get a string with the names of these columns, I would like to get 'F2,F7' as a return value. This is a temporary table and column names and the number of columns is unknown. I need some very generic function to extract the column names containing NULLs

NOTE: I know it is fairy easy in Oracle using some system objects (i.e. all_tab_columns, etc), not sure if possible in SQL Server as well.

Thank you.

Improve this question
4
  • You actually should redesign your table. That is NOT how you do it Commented Oct 19, 2016 at 17:06
  • Why should I redesign the table? This is not a question about table structure, it is rather related to table NULL values Commented Oct 19, 2016 at 17:16
  • You can use UNPIVOT and the XML contcat trick. Commented Oct 19, 2016 at 17:20
  • Interesting, can you please provide an example? Commented Oct 19, 2016 at 17:22

3 Answers 3

Reset to default
4

To list all non-nullable columns in the 'dbo.Employee' Table in the database, run the following query:

SELECT TABLE_CATALOG AS Database_Name, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'Employee'
AND IS_NULLABLE = 'NO'
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

4 
SELECT t.name, c.name, c.is_nullable 
FROM sys.tables t 
JOIN sys.columns c on t.object_id = c.object_id
WHERE t.name = 'YourTableNameHere'
  AND c.is_nullable = 0

If you are on MS SQL Server and trying to avoid INFORMATION_SCHEMA.

Improve this answer

Comments

3

Not sure why you need this but something like this should help you

Select CASE WHEN Len(res) > 0 THEN LEFT(res, Len(res) - 1) ELSE '' END AS result
From 
(
select case when Count(F1)= 0 then 'F1,' else '' End +
       case when Count(F2)= 0 then 'F2,' else '' End +
       case when Count(F3)= 0 then 'F3,' else '' End +
            .....
       case when Count(F10)= 0 then 'F10,' else '' End 
       End as res
From yourtable
) A

Here is dynamic approach that works for unknown column names 

DECLARE @sql VARCHAR(max) =''

SET @sql = ' Select CASE WHEN Len(res) > 0 THEN LEFT(res, Len(res) - 1) ELSE '''' END AS result
From 
(
select'
SET @sql += (SELECT ' case when Count(' + COLUMN_NAME + ')= 0 then ''' + COLUMN_NAME + ','' else '''' End+'
             FROM   INFORMATION_SCHEMA.COLUMNS
             WHERE  table_name = 'TableA'
             FOR xml path (''))
SET @sql = LEFT(@sql, Len(@sql) - 1)
SET @sql += ' From yourtable ) A (res)'

--SELECT @sql
EXEC (@sql)
Improve this answer

12 Comments

@vkp - Probably you should say why it will not work to improve my answer
problem1-case returns a value on the first true condition, other conditions will be ignored. problem 2- you should use count(case when f1 is not null then 1 end) = 0
I didn't mention this: this is a temporary table and column names and the number of columns is unknown. I need some very generic function to extract the column names containing NULLs
@vkp - Problem1 agreed and updated.. regarding problem2 how it differs from COUNT(F1)
sorry about problem 2.. the updated query should work well.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.