SQL: выберите столбцы только с значениями NULL

Ответ 1

Вот версия sql 2005 или более поздняя. Замените ADDR_Address своим именем.

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

Ответ 2

SELECT cols
FROM table
WHERE cols IS NULL

Ответ 3

Это должно дать вам список всех столбцов в таблице "Человек", который имеет только значения NULL. Вы получите результаты как несколько наборов результатов, которые либо пусты, либо содержат имя одного столбца. Вам нужно заменить "Person" в двух местах, чтобы использовать его с другой таблицей.

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

Ответ 4

Или вы хотите просто увидеть, имеет ли столбец только значения NULL (и, следовательно, вероятно, не используется)?

Дальнейшее разъяснение вопроса может помочь.

EDIT: Хорошо.. вот какой-то действительно грубый код, чтобы вы пошли...

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

Да, есть более простые способы, но у меня есть встреча, чтобы идти прямо сейчас. Удачи!

Ответ 5

Вот обновленная версия запроса Bryan за 2008 год и позже. Он использует INFORMATION_SCHEMA.COLUMNS, добавляет переменные для схемы таблицы и имени таблицы. Тип данных столбца был добавлен в вывод. Включение типа данных столбца помогает при поиске столбца определенного типа данных. Я не добавил ширину столбца или что-то еще.

Для вывода используется RAISERROR... WITH NOWAIT, поэтому текст будет отображаться сразу, а не сразу (по большей части) в конце, как это делает 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;

Ответ 6

Вы можете сделать:

select 
  count(<columnName>)
from
  <tableName>

Если count возвращает 0, это означает, что все строки в этом столбце имеют значение NULL (или в таблице нет всех строк)

можно изменить на

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

Если вы хотите его автоматизировать, вы можете использовать системные таблицы для итерации имен столбцов в интересующей вас таблице.

Ответ 7

Я бы также рекомендовал искать поля, у которых все одинаковое значение, а не только NULL.

То есть для каждого столбца в каждой таблице выполните запрос:

SELECT COUNT(DISTINCT field) FROM tableName

и сосредоточьтесь на тех, которые в результате возвращают 1.

Ответ 8

Если вам нужно перечислить все строки, где все значения столбца NULL, я бы использовал функцию COLLATE. Это принимает список значений и возвращает первое ненулевое значение. Если вы добавите все имена столбцов в список, используйте IS NULL, вы должны получить все строки, содержащие только нули.

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

У вас не должно быть таблиц со ВСЕМИ columns null, так как это означает, что у вас нет primary key (не разрешено быть NULL). Отсутствие первичного ключа - это то, чего следует избегать; это нарушает первую нормальную форму.

Ответ 9

Не уверен, что в 2005 году, но 2008 съел его:

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)

Ответ 10

Вам нужно будет перебрать набор столбцов и проверить их. Вы должны иметь возможность получить список всех столбцов с помощью команды таблицы DESCRIBE.

Псевдо-код:

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;

Я знаю, что это кажется немного противоречивым, но SQL не предоставляет собственный метод выбора столбцов, только строки.

Ответ 11

Попробуйте это -

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)

Ответ 12

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;

Ответ 13

Обновленная версия "user2466387" с дополнительным небольшим тестом, который может повысить производительность, потому что бесполезно тестировать столбцы с нулевым значением:

AND IS_NULLABLE = 'YES'

Полный код:

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;

Ответ 14

Здесь я создал скрипт для любого вида таблицы SQL. скопируйте эту хранимую процедуру, создайте ее в своей среде и запустите эту хранимую процедуру со своей таблицей.

exec [dbo].[SP_RemoveNullValues] 'Your_Table_Name'

хранимая процедура

GO
/****** Object:  StoredProcedure [dbo].[SP_RemoveNullValues]    Script Date: 09/09/2019 11:26:53 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- akila liyanaarachchi
Create procedure [dbo].[SP_RemoveNullValues](@PTableName Varchar(50) ) as 
begin


DECLARE Cussor CURSOR FOR 
SELECT COLUMN_NAME,TABLE_NAME,DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @PTableName  

OPEN Cussor;

Declare @ColumnName Varchar(50)
Declare @TableName  Varchar(50)
Declare @DataType Varchar(50)
Declare @Flage  int 

FETCH NEXT FROM Cussor INTO @ColumnName,@TableName,@DataType
WHILE @@FETCH_STATUS = 0
BEGIN

set @Flage=0


If(@DataType in('bigint','numeric','bit','smallint','decimal','smallmoney','int','tinyint','money','float','real'))
begin
set @Flage=1
end 
If(@DataType in('date','atetimeoffset','datetime2','smalldatetime','datetime','time'))
begin
set @Flage=2
end 
If(@DataType in('char','varchar','text','nchar','nvarchar','ntext'))
begin
set @Flage=3
end 

If(@DataType in('binary','varbinary'))
begin
set @Flage=4
end 



DECLARE @SQL VARCHAR(MAX) 

if  (@Flage in(1,4))
begin 

SET @SQL ='  update ['[email protected]+'] set ['[email protected]+']=0 where ['[email protected]+'] is null'
end 

if  (@Flage =3)
begin 

SET @SQL ='  update ['[email protected]+'] set ['[email protected]+'] = '''' where ['[email protected]+'] is null '
end 

if  (@Flage =2)
begin 

SET @SQL ='  update ['[email protected]+'] set ['[email protected]+'] ='+'''1901-01-01 00:00:00.000'''+' where ['[email protected]+'] is null '
end 


EXEC(@SQL)



FETCH NEXT FROM Cussor INTO @ColumnName,@TableName,@DataType
END

CLOSE Cussor
DEALLOCATE Cussor

END

Ответ 15

Возможно, вам придется немного уточнить. Чего вы действительно пытаетесь достичь? Если вы действительно хотите узнать имена столбцов, которые содержат только нулевые значения, вам придется прокручивать схему и выполнять динамический запрос на основе этого.

Я не знаю, какую СУБД вы используете, поэтому я поставлю здесь псевдокод.

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