Reputation:
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: 6596
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
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
Reputation: 93764
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