SQL: selezionare solo le colonne con valori NULL

Come seleziono tutte le colonne in una tabella che contengono solo valori NULL per tutte le righe? Sto usando MS SQL Server 2005 . Sto cercando di scoprire quali colonne non vengono utilizzate nella tabella in modo che io possa eliminarle.

Ecco la versione di SQL 2005 o successiva: Sostituisci ADDR_Address con il tuo 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 
 SELECT cols FROM table WHERE cols IS NULL 

Questo dovrebbe darti un elenco di tutte le colonne nella tabella “Persona” che ha solo valori NULL. Otterrai i risultati come set di risultati multipli, vuoti o contenenti il ​​nome di una singola colonna. È necessario sostituire “Persona” in due punti per usarlo con un’altra tabella.

 DECLARE crs CURSOR LOCAL FAST_FORWARD FOR SELECT name FROM syscolumns WHERE id=OBJECT_ID('Person') OPEN crs DECLARE @name sysname FETCH NEXT FROM crs INTO @name WHILE @@FETCH_STATUS = 0 BEGIN EXEC('SELECT ''' + @name + ''' WHERE NOT EXISTS (SELECT * FROM Person WHERE ' + @name + ' IS NOT NULL)') FETCH NEXT FROM crs INTO @name END CLOSE crs DEALLOCATE crs 

O vuoi solo vedere se una colonna ha solo valori NULL (e, quindi, è probabilmente inutilizzata)?

Un ulteriore chiarimento della domanda potrebbe aiutare.

EDIT: Ok .. ecco un codice veramente approssimativo per farti andare …

 SET NOCOUNT ON DECLARE @TableName Varchar(100) SET @TableName='YourTableName' CREATE TABLE #NullColumns (ColumnName Varchar(100), OnlyNulls BIT) INSERT INTO #NullColumns (ColumnName, OnlyNulls) SELECT c.name, 0 FROM syscolumns c INNER JOIN sysobjects o ON c.id = o.id AND o.name = @TableName AND o.xtype = 'U' DECLARE @DynamicSQL AS Nvarchar(2000) DECLARE @ColumnName Varchar(100) DECLARE @RC INT SELECT TOP 1 @ColumnName = ColumnName FROM #NullColumns WHERE OnlyNulls=0 WHILE @@ROWCOUNT > 0 BEGIN SET @RC=0 SET @DynamicSQL = 'SELECT TOP 1 1 As HasNonNulls FROM ' + @TableName + ' (nolock) WHERE ''' + @ColumnName + ''' IS NOT NULL' EXEC sp_executesql @DynamicSQL set @[email protected]@rowcount IF @RC=1 BEGIN SET @DynamicSQL = 'UPDATE #NullColumns SET OnlyNulls=1 WHERE ColumnName=''' + @ColumnName + '''' EXEC sp_executesql @DynamicSQL END ELSE BEGIN SET @DynamicSQL = 'DELETE FROM #NullColumns WHERE ColumnName=''' + @ColumnName+ '''' EXEC sp_executesql @DynamicSQL END SELECT TOP 1 @ColumnName = ColumnName FROM #NullColumns WHERE OnlyNulls=0 END SELECT * FROM #NullColumns DROP TABLE #NullColumns SET NOCOUNT OFF 

Sì, ci sono modi più semplici, ma ho un incontro da fare adesso. In bocca al lupo!

Tu puoi fare:

 select count() from  

Se il conteggio restituisce 0 significa che tutte le righe in quella colonna sono tutte NULL (o non ci sono righe nella tabella)

può essere cambiato in

 select case(count()) when 0 then 'Nulls Only' else 'Some Values' end from  

Se vuoi automatizzarlo puoi usare le tabelle di sistema per iterare i nomi delle colonne nella tabella che ti interessa

Ecco una versione aggiornata della query di Bryan per il 2008 e successivi. Usa INFORMATION_SCHEMA.COLUMNS, aggiunge variabili per lo schema della tabella e il nome della tabella. Il tipo di dati della colonna è stato aggiunto all’output. Includere il tipo di dati della colonna aiuta a cercare una colonna di un particolare tipo di dati. Non ho aggiunto le larghezze della colonna o altro.

Per l’output RAISERROR … WITH NOWAIT viene utilizzato in modo che il testo venga visualizzato immediatamente anziché tutto in una volta (per la maggior parte) alla fine come fa PRINT.

 SET NOCOUNT ON; DECLARE @ColumnName sysname ,@DataType nvarchar(128) ,@cmd nvarchar(max) ,@TableSchema nvarchar(128) = 'dbo' ,@TableName sysname = 'TableName'; DECLARE getinfo CURSOR FOR SELECT c.COLUMN_NAME ,c.DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS AS c WHERE c.TABLE_SCHEMA = @TableSchema AND c.TABLE_NAME = @TableName; OPEN getinfo; FETCH NEXT FROM getinfo INTO @ColumnName, @DataType; WHILE @@FETCH_STATUS = 0 BEGIN SET @cmd = N'IF NOT EXISTS (SELECT * FROM ' + @TableSchema + N'.' + @TableName + N' WHERE [' + @ColumnName + N'] IS NOT NULL) RAISERROR(''' + @ColumnName + N' (' + @DataType + N')'', 0, 0) WITH NOWAIT;'; EXECUTE (@cmd); FETCH NEXT FROM getinfo INTO @ColumnName, @DataType; END; CLOSE getinfo; DEALLOCATE getinfo; 

Vorrei anche raccomandare di cercare i campi che hanno tutti lo stesso valore, non solo NULL.

Cioè, per ogni colonna in ogni tabella fai la query:

 SELECT COUNT(DISTINCT field) FROM tableName 

e concentrarsi su quelli che restituiscono 1 come risultato.

Se è necessario elencare tutte le righe in cui tutti i valori delle colonne sono NULL , quindi utilizzare la funzione COLLATE . Questo richiede un elenco di valori e restituisce il primo valore non nullo. Se si aggiungono tutti i nomi delle colonne all’elenco, quindi utilizzare IS NULL , è necessario ottenere tutte le righe contenenti solo valori null.

 SELECT * FROM MyTable WHERE COLLATE(Col1, Col2, Col3, Col4......) IS NULL 

Non dovresti avere nessuna tabella con TUTTE le columns nulle, poiché ciò significa che non hai una primary key (non può essere null ). Non avere una chiave primaria è qualcosa da evitare; questo rompe la prima forma normale.

 SELECT t.column_name FROM user_tab_columns t WHERE t.nullable = 'Y' AND t.table_name = 'table name here' AND t.num_distinct = 0; 

Non sono sicuro del 2005, ma il 2008 lo ha mangiato:

 USE [DATABASE_NAME] -- ! GO DECLARE @SQL NVARCHAR(MAX) DECLARE @TableName VARCHAR(255) SET @TableName = 'TABLE_NAME' -- ! SELECT @SQL = ( SELECT CHAR(10) +'DELETE FROM ['+t1.TABLE_CATALOG+'].['+t1.TABLE_SCHEMA+'].['+t1.TABLE_NAME+'] WHERE ' +( SELECT CASE t2.ORDINAL_POSITION WHEN (SELECT MIN(t3.ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS t3 WHERE t3.TABLE_NAME=t2.TABLE_NAME) THEN '' ELSE 'AND ' END +'['+COLUMN_NAME+'] IS NULL' AS 'data()' FROM INFORMATION_SCHEMA.COLUMNS t2 WHERE t2.TABLE_NAME=t1.TABLE_NAME FOR XML PATH('') ) AS 'data()' FROM INFORMATION_SCHEMA.TABLES t1 WHERE t1.TABLE_NAME = @TableName FOR XML PATH('') ) SELECT @SQL -- EXEC(@SQL) 

Potrebbe essere necessario chiarire un po ‘. Cosa stai cercando di realizzare davvero? Se vuoi veramente scoprire i nomi delle colonne che contengono solo valori nulli, allora dovrai scorrere il registro e fare una query dynamic basata su questo.

Non so quale DBMS stai usando, quindi inserirò qualche pseudo-codice qui.

 for each col begin @cmd = 'if not exists (select * from tablename where ' + col + ' is not null begin print ' + col + ' end' exec(@cmd) end 

Dovrai ricorrere al set di colonne e controllare ciascuna di esse. Dovresti essere in grado di ottenere un elenco di tutte le colonne con un comando table DESCRIBE.

Pseudo-codice:

 foreach $column ($cols) { query("SELECT count(*) FROM table WHERE $column IS NOT NULL") if($result is zero) { # $column contains only null values" push @onlyNullColumns, $column; } else { # $column contains non-null values } } return @onlyNullColumns; 

So che questo sembra un po ‘controintuitivo, ma SQL non fornisce un metodo nativo per selezionare colonne, solo righe.

Prova questo –

 DECLARE @table VARCHAR(100) = 'dbo.table' DECLARE @sql NVARCHAR(MAX) = '' SELECT @sql = @sql + 'IF NOT EXISTS(SELECT 1 FROM ' + @table + ' WHERE ' + c.name + ' IS NOT NULL) PRINT ''' + c.name + '''' FROM sys.objects o JOIN sys.columns c ON o.[object_id] = c.[object_id] WHERE o.[type] = 'U' AND o.[object_id] = OBJECT_ID(@table) AND c.is_nullable = 1 EXEC(@sql) 

Una versione aggiornata della versione ‘user2466387’, con un piccolo test aggiuntivo che può migliorare le prestazioni, perché è inutile testare colonne non annullabili:

 AND IS_NULLABLE = 'YES' 

Il codice completo:

 SET NOCOUNT ON; DECLARE @ColumnName sysname ,@DataType nvarchar(128) ,@cmd nvarchar(max) ,@TableSchema nvarchar(128) = 'dbo' ,@TableName sysname = 'TableName'; DECLARE getinfo CURSOR FOR SELECT c.COLUMN_NAME ,c.DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS AS c WHERE c.TABLE_SCHEMA = @TableSchema AND c.TABLE_NAME = @TableName AND IS_NULLABLE = 'YES'; OPEN getinfo; FETCH NEXT FROM getinfo INTO @ColumnName, @DataType; WHILE @@FETCH_STATUS = 0 BEGIN SET @cmd = N'IF NOT EXISTS (SELECT * FROM ' + @TableSchema + N'.' + @TableName + N' WHERE [' + @ColumnName + N'] IS NOT NULL) RAISERROR(''' + @ColumnName + N' (' + @DataType + N')'', 0, 0) WITH NOWAIT;'; EXECUTE (@cmd); FETCH NEXT FROM getinfo INTO @ColumnName, @DataType; END; CLOSE getinfo; DEALLOCATE getinfo;