CODEWITHSUNDEEP
sqlsql-server
user5326167
user5326167

Reputation:

Get all not NULL column names from a SQL Server table

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.

Upvotes: 1

Views: 6604

Answers (3)

discosammy
discosammy

Reputation: 341

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.

Upvotes: 3

Nilesh Umaretiya
Nilesh Umaretiya

Reputation: 786

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'

Upvotes: 4

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

Reputation: 93754

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)

Upvotes: 3

Related Questions