我有一个324列的大表，我想得到一个没有任何值的列列表

Question

我想要列列表和它的表名在数据库中的列具有所有空值。

此表太大，有些列只有空值。

我想要一个存储过程，列出表中没有任何数据的列（即NULL）。

这样我就可以削减列的数量。

CREATE TABLE dbo.ngkbm_template_data_sets_
(
    seq_no uniqueidentifier NOT NULL,
    practice_id char(4) NULL,
    created_by int NOT NULL,
    create_timestamp datetime NOT NULL,
    modified_by int NOT NULL,
    modify_timestamp datetime NOT NULL,
    create_timestamp_tz smallint NULL,
    modify_timestamp_tz smallint NULL,
    row_timestamp timestamp NOT NULL,
    chk_combo_med varchar(1) NULL,
    chk_inactive_ind varchar(1) NULL,
    chk_label_values int NULL,
    kbm_ind varchar(1) NULL,
    opt_sp int NULL,
    txt_cursor_hold varchar(1) NULL,
    txt_data_set varchar(50) NULL,
    txt_description_1 varchar(75) NULL,
    txt_description_10 varchar(75) NULL,
    txt_description_11 varchar(75) NULL,
    txt_description_12 varchar(100) NULL,
    txt_description_13 varchar(100) NULL,
    txt_description_14 varchar(75) NULL,
    txt_description_15 varchar(75) NULL,
    txt_description_16 varchar(75) NULL,
    txt_description_17 varchar(75) NULL
)

我只是展示了几个专栏，这样你就可以阅读了。原始表有324列和数百万行数据。对于该代码来说，性能不是一个紧迫的问题。我需要这个只是为了内部目的。

这是mysql解决方案，我需要一个SQL服务器解决方案

SET group_concat_max_len = 4294967295; -- to overcome default 1KB limitation

SELECT CONCAT(
         'SELECT * FROM ('
       ,  GROUP_CONCAT(
            'SELECT ', QUOTE(TABLE_NAME), ' AS `table`,'
          , 'IF('
          ,   'COUNT(`', REPLACE(COLUMN_NAME, '`', '``'), '`),'
          ,   'NULL,'
          ,    QUOTE(COLUMN_NAME)
          , ') AS `column` '
          , 'FROM `', REPLACE(TABLE_NAME, '`', '``'), '`'
          SEPARATOR ' UNION ALL '
         )
       , ') t WHERE `column` IS NOT NULL'
       )
INTO   @sql
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_SCHEMA = DATABASE();

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Answer 1

以下脚本围绕数据库中的所有用户表循环，并为每个表中的每列打印一行，告诉您它是否包含所有空值：

DECLARE @SchemaName nvarchar(max)
DECLARE @TableName nvarchar(max)
DECLARE @ColumnName nvarchar(max)
DECLARE @SQL nvarchar(max)

DECLARE TableCur cursor for
select TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES

    SET NOCOUNT ON

    OPEN TableCur

    FETCH NEXT FROM TableCur into @SchemaName, @TableName

    WHILE @@FETCH_STATUS = 0
    BEGIN

        DECLARE ColumnCur CURSOR FOR
        SELECT COLUMN_NAME
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = @TableName

        OPEN ColumnCur

        FETCH NEXT FROM ColumnCur into @ColumnName

        WHILE @@FETCH_STATUS = 0
        BEGIN

            SET @SQL = 'DECLARE @COUNTER int; SELECT @COUNTER = COUNT([' + @ColumnName + ']) FROM ' + @SchemaName + '.' + @TableName + ';'
            SET @SQL = @SQL + ' IF @COUNTER = 0 BEGIN SELECT ' + '''' + 'The column [' + @SchemaName + '.' + @TableName + '.'  + @ColumnName + '] has all null values' + '''' + ' END'
            SET @SQL = @SQL + ' ELSE BEGIN SELECT ' + '''' + 'The column ['  + @SchemaName + '.' + @TableName + '.' + @ColumnName + '] has some non-null values' + '''' + ' END'

            Exec sp_executesql @SQL


            FETCH NEXT FROM ColumnCur into @ColumnName

        END

        CLOSE ColumnCur
        DEALLOCATE ColumnCur

        FETCH NEXT FROM TableCur into @SchemaName, @TableName

    END

    CLOSE TableCur
    DEALLOCATE TableCur

希望这有帮助，

当做

Answer 2

基本上，你需要解开这些值。有几种方法。这是一个非常简单的问题：

select name
from ngkbm_template_data_sets_ t outer apply
     (values ('txt_description_1', txt_description_1),
             ('txt_description_2', txt_description_2),
             . . .
     ) cols(name, val)
group by name
having count(val) = 0;

是的，这确实需要列出所有列。我建议使用信息模式。列和/或您最喜欢的电子表格，以生成由表示的代码 。

Answer 3

下面是一个快速存储过程，它将对提供的表中的所有列执行NULL检查。

Create Procedure spGetColumnsWithAllNullValues
( 
    @Database   Varchar (100),
    @Schema     Varchar (100),
    @Table      Varchar (100)
)
As Begin

    Declare @Column Varchar (100)

    Declare @Columns Table
    (
        ColumnName Varchar (100)
    )

    Declare @Results Table
    (
        ColumnName Varchar (100)
    )

    Declare @Temp Table
    (
        Result Bit
    )

    Insert  @Columns
    Select  COLUMN_NAME
    From    INFORMATION_SCHEMA.COLUMNS
    Where   IS_NULLABLE = 'YES'
    And     TABLE_CATALOG = @Database
    And     TABLE_SCHEMA = @Schema
    And     TABLE_NAME = @Table

    Declare cur Cursor For
    Select  ColumnName
    From    @Columns

    Open cur

    While (1 = 1)
    Begin
        Fetch Next From cur Into @Column

        If (@@FETCH_STATUS <> 0) Break

        Declare @sql NVarchar(Max) = N'Select Case When Exists (Select * From ' 
                                    + QuoteName(@Database) + '.' 
                                    + QuoteName(@Schema) + '.' 
                                    + QuoteName(@Table) 
                                    + ' Where ' + QuoteName(@Column) + ' Is Not Null) Then 0 Else 1 End'

        Delete @Temp
        Insert @Temp Execute (@sql)

        Insert  @Results
                (ColumnName)
        Select  @Column
        From    @Temp
        Where   Result = 1
    End

    Close cur        
    Deallocate cur

    Select  ColumnName
    From    @Results
    Order By ColumnName

End

您只需为其提供数据库名、模式和表名。你可以根据需要调整。

演示表：

A           B           DummyColumn
----------- ----------- -----------
1           1           NULL
1           2           NULL
1           3           NULL
2           5           NULL
2           4           NULL
3           NULL        NULL

用法：

Execute spGetColumnsWithAllNullValues 'Sandbox', 'dbo', 'B'

输出：

ColumnName
----------------
DummyColumn