Menu
How do I select all the columns in a table that only contain NULL values for all the rows? I'm using MS SQL Server 2005. I'm trying to find out which columns are not used in the table so I can delete them.
363
select column_name from user_tab_columns where table_name='Table_name' and num_nulls>=1; Just by simple query you will get those two columns.
SELECT column_names FROM table_name WHERE column_name IS NOT NULL; Query: SELECT * FROM Student WHERE Name IS NOT NULL AND Department IS NOT NULL AND Roll_No IS NOT NULL; To exclude the null values from all the columns we used AND operator.
Use <=> (null-safe equality operator) negated comparison which returns FALSE in case one of the operands is null but TRUE when both are null and both operands have equal non-null values.
Here is the sql 2005 or later version: Replace ADDR_Address with your tablename.
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 = 'ADDR_Address' OPEN getinfo FETCH NEXT FROM getinfo into @col WHILE @@FETCH_STATUS = 0 BEGIN SELECT @cmd = 'IF NOT EXISTS (SELECT top 1 * FROM ADDR_Address WHERE [' + @col + '] IS NOT NULL) BEGIN print ''' + @col + ''' end' EXEC(@cmd) FETCH NEXT FROM getinfo into @col END CLOSE getinfo DEALLOCATE getinfo If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
