SQL 如何查找哪些列没有任何数据（所有值均为 NULL）？

Question

提问by jrara

I have several tables in a database. I would like to find which columns (in which tables) don't have any values (all NULL in a column). I the example below, the result should be

我在一个数据库中有几个表。我想找到哪些列（在哪些表中）没有任何值（列中全部为 NULL）。我下面的例子，结果应该是

TestTable1 --> Var2
TestTable2 --> Variable1

I don't have any idea how to create this kind of query. Your help is most appreciated!

我不知道如何创建这种查询。非常感谢您的帮助！

--create first table
create table dbo.TestTable1 (
sur_id int identity(1,1) not null primary key,
var1 int null,
var2 int null
)
go

--insert some values
insert into dbo.TestTable1 (var1) 
    select 1 union all select 2 union all select 3

--create second table
create table dbo.TestTable2 (
sur_id int identity(1,1) not null primary key,
variable1 int null,
variable2 int null
)

--and insert some values
insert into dbo.TestTable2 (variable2) 
    select 1 union all select 2 union all select 3

Answer 1

采纳答案by Andomar

For a single column, count(ColumnName)returns the number of rows where ColumNameis not null:

对于单列，count(ColumnName)返回ColumName不为空的行数：

select  count(TheColumn)
from    YourTable

You can generate a query for all columns. Per Martin's suggestion, you can exclude columns that cannot be null with is_nullable = 1. For example:

您可以为所有列生成查询。根据 Martin 的建议，您可以使用排除不能为空的列is_nullable = 1。例如：

select  'count(' + name + ') as ' + name + ', '
from    sys.columns
where   object_id = object_id('YourTable')
        and is_nullable = 1

If the number of tables is large, you can generate a query for all tables in a similiar way. The list of all tables is in sys.tables.

如果表的数量很大，您可以以类似的方式生成所有表的查询。所有表的列表在sys.tables.

Answer 2

回答by Dai

Here's a script I wrote to do the same thing, it's a two-step manual process:

这是我写的一个脚本来做同样的事情，这是一个两步手动过程：

Run this script in SSMS and select all of the rows in the Results pane:

在 SSMS 中运行此脚本并选择结果窗格中的所有行：

SELECT
'SELECT
    COUNT( DISTINCT [' + COLUMN_NAME + ']) AS UniqueValues,
    ''' + TABLE_NAME + '.' + COLUMN_NAME + ''' AS ColumnName
FROM
    [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']

UNION ALL
'
FROM
    INFORMATION_SCHEMA.COLUMNS
ORDER BY
    TABLE_NAME,
    COLUMN_NAME

Paste the results into a new query window. Scroll to the very bottom and remove the trailing UNION ALLstatement. It will look like this:

将结果粘贴到新的查询窗口中。滚动到最底部并删除尾部UNION ALL语句。它看起来像这样：

SELECT   COUNT( DISTINCT [ModifiedByUserId]) AS UniqueValues,   'Inspections.ModifiedByUserId' AS ColumnName  FROM   [dbo].[Inspections]     UNION ALL  
SELECT   COUNT( DISTINCT [Notes]) AS UniqueValues,   'Inspections.Notes' AS ColumnName  FROM   [dbo].[Inspections]     UNION ALL  
SELECT   COUNT( DISTINCT [PublicPassword]) AS UniqueValues,   'Inspections.PublicPassword' AS ColumnName  FROM   [dbo].[Inspections]     UNION ALL  
SELECT   COUNT( DISTINCT [ShopId]) AS UniqueValues,   'Inspections.ShopId' AS ColumnName  FROM   [dbo].[Inspections]     UNION ALL  
SELECT   COUNT( DISTINCT [Status]) AS UniqueValues,   'Inspections.Status' AS ColumnName  FROM   [dbo].[Inspections]     UNION ALL  
SELECT   COUNT( DISTINCT [SupervisorUserId]) AS UniqueValues,   'Inspections.SupervisorUserId' AS ColumnName  FROM   [dbo].[Inspections]     UNION ALL

Run the query. It took about 6 minutes to run on a 300-column database. It will be faster or slow depending on how many indexes are being used.

运行查询。在一个 300 列的数据库上运行大约需要 6 分钟。它会更快或更慢，具体取决于正在使用的索引数量。

Answer 3

回答by Doug Chamberlain

Updated....Okay I had way too much fun with this

更新了……好吧，我玩得很开心

THe Proc accepts two parameters, the table to search & the Criteria to apply. you can pass essentially and where clause to the second parameter. I wrote the proc to interpret double quotes back to single quotes....again this was built off of the original developers concepts.

Proc 接受两个参数，要搜索的表和要应用的标准。您可以将本质上和 where 子句传递给第二个参数。我编写了 proc 来将双引号解释回单引号……这又是建立在最初的开发人员概念之上的。

GO
/****** Object:  StoredProcedure [dbo].[SearchAllTables]    Script Date: 05/04/2011 14:29:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROC [dbo].[SearchAllTables2] ( @SEARCH_TABLE NVARCHAR(255), @CONDITION AS NVARCHAR(MAX) ) AS
BEGIN

-- Copyright ? 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi -- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @COND_STR NVARCHAR(MAX)
SET @TableName = '' 
--SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @CONDITION = REPLACE(@CONDITION,'"','''')
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE'
AND
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0 AND TABLE_NAME = @SEARCH_TABLE
) WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) 
BEGIN SET @ColumnName = ( 
SELECT MIN(QUOTENAME(COLUMN_NAME)) 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) AND 
TABLE_NAME = PARSENAME(@TableName, 1) AND QUOTENAME(COLUMN_NAME) > @ColumnName ) 
IF @ColumnName IS NOT NULL 
BEGIN 
SET @COND_STR = REPLACE(@CONDITION,'''','"')
INSERT INTO #Results 
EXEC ( 'SELECT  DISTINCT ''' + @TableName + '.' + @ColumnName + ''',''' + @COND_STR + ''' AS CONDITION FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' ' + @CONDITION)
PRINT ( 'SELECT  DISTINCT ''' + @TableName + '.' + @ColumnName + ''',''' + @COND_STR + ''' AS CONDITION FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' ' + @CONDITION)
END 
END 
END 
    SELECT ColumnName, ColumnValue 
    FROM #Results 
END
GO
-- to execute

exec [SearchAllTables2] 'TABLENAME','LIKE "%DOUG%"' -- double quotes are automatically escaped to single quotes...

Original code modified from copyright below....only using portions.

从以下版权修改的原始代码......仅使用部分。

GO
/****** Object:  StoredProcedure [dbo].[SearchAllTables]    Script Date: 05/04/2011 14:29:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROC [dbo].[SearchAllTables2] ( @TABLE_NAME NVARCHAR(255) ) AS
BEGIN

-- Copyright ? 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi -- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = '' 
--SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE'
AND
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0 AND TABLE_NAME = @TABLE_NAME
) WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) 
BEGIN SET @ColumnName = ( 
SELECT MIN(QUOTENAME(COLUMN_NAME)) 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) AND 
TABLE_NAME = PARSENAME(@TableName, 1) AND QUOTENAME(COLUMN_NAME) > @ColumnName ) 
IF @ColumnName IS NOT NULL 
BEGIN 
INSERT INTO #Results 
EXEC ( 'SELECT  DISTINCT ''' + @TableName + '.' + @ColumnName + ''', ''IS NULL'' FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' IS NULL ')--LIKE ' + @SearchStr2 ) 
--PRINT ( 'SELECT  DISTINCT ''' + @TableName + '.' + @ColumnName + ''', ''IS NOT NULL'' FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE  ' + @ColumnName + ' IS NOT NULL ')--LIKE ' + @SearchStr2 ) 
END 
END 
END 
    SELECT ColumnName, ColumnValue 
    FROM #Results 
END
GO
-- to execute

exec [SearchAllTables2] 'Master'

Answer 4

回答by StuartN

This is way too useful not to be put into a handy little system proc - especially if you've just inherited a legacy database, and are wondering what columns you can drop or ignore.

这太有用了，不能放在一个方便的小系统过程中 - 特别是如果你刚刚继承了一个遗留数据库，并且想知道你可以删除或忽略哪些列。

/*
Show the count of not-null values in a table
*/
create proc sp_aaShowAllNullColumns @tableName varchar(255) as
begin
set nocount on

declare @sql nvarchar(4000)
declare @cols nvarchar(4000)
declare @tcols table( colbit nvarchar(255) )

insert @tcols
select 'count(' + name + ') as ' + name + ', ' as colbit
from    sys.columns
where   object_id = object_id(@tableName)
and is_nullable = 1 

select @cols = coalesce( @cols, ', ', '' ) + colbit from @tcols
select @cols = substring( @cols, 1, (len(@cols) - 1) )
select @cols = isnull( @cols, '' )

select @sql = 'select count(*) as Rows' + @cols + ' from ' + @tableName
exec sp_executeSql @sql

end
go

exec sys.sp_MS_marksystemobject 'sp_aaShowAllNullColumns'
go

use Bookshop
go
exec sp_aaShowAllNullColumns 'Books'
go

Answer 5

回答by peak

Here's a bash script that, for all non-empty tables in an SQLite database (or for all the specified tables in such a database), identifies the all-NULL columns. The same technique can be used in the programming language of your choice assuming it can talk to an SQLite database.

这是一个 bash 脚本，它为 SQLite 数据库中的所有非空表（或此类数据库中的所有指定表）标识全 NULL 列。假设它可以与 SQLite 数据库通信，则可以在您选择的编程语言中使用相同的技术。

#!/bin/bash

function help {
    cat <<EOF
Syntax: ##代码## databasefile [table ...]

If no tables are specified, then for each non-empty user table in the
specified SQLite database row, this script will emit the column names
of those columns in which all the values are NULL.  If any tables are
specified, only the specified tables are scanned.

The script is written to make it easy to modify the criteria and the output.

Thanks to SQL, two passes are required per table, and if no tables are
specified, an additional invocation of sqlite3 is required.

Column names are written in the form: tablename.columnname

Requirements:
  sqlite3 on the $PATH

Options: 
 -v | --verbose :: emit additional information

EOF
}

while [ "" ]
do case "" in
      -h | --help | "" ) help
           exit
           ;;
      -v | --verbose ) VERBOSE=1
           shift
           ;;
      * ) break
      ;;
  esac
done

function verbose { if [ "$VERBOSE" ] ; then echo "$@" >&2 ; fi ; }

db=""
shift

if [ ! -s "$db" ] ; then echo "##代码## : $db not found" ; exit ; fi

# To prevent loading ~/.sqliterc specify -init ""

# Global: db
function nullcolumns {
  local table=""
  local count column field nulls
  ( read count
    if [ -n "$count" ] ; then
        verbose "Row count for $table: $count"
    if [ "$count" -gt 0 ] ; then
          while read column ; do
          echo "SELECT '$column', * FROM
                  (SELECT COUNT(*) FROM $table WHERE '$column' IS NULL);"
          done |
          sqlite3 -readonly "$db" | while IFS="|" read field nulls ; do
            verbose $table.$field ... $nulls
            if [ "$nulls" -eq $count ] ; then echo "$table.$field" ; fi
          done
        else cat > /dev/null
        fi
    else cat > /dev/null
    fi ) < <(sqlite3 -readonly "$db" "select count(*) from '$table'; 
                                      select name from pragma_table_info( '$table' )")
}

if [ $# = 0 ] ; then
    sqlite3 -readonly "$db" .tables | while read table ; do
    nullcolumns "$table"
    done
else
    for table ; do
    nullcolumns "$table"
    done
fi

SQL 如何查找哪些列没有任何数据（所有值均为 NULL）？

提问by jrara

采纳答案by Andomar

回答by Dai

回答by Doug Chamberlain

回答by StuartN

回答by peak

相关推荐

最近更新

标签

SQL 如何查找哪些列没有任何数据（所有值均为 NULL）？

提问by jrara

采纳答案by Andomar

回答by Dai

回答by Doug Chamberlain

回答by StuartN

回答by peak

相关推荐

是否有可以在列中查找非整数的 sql 条件？

SQL 从oracle sql中的列值中删除单引号

SQL WHERE ID IN (id1, id2, ..., idn)

SQL 如果两个表在不同的模式中，如何连接它们

相关推荐

最近更新

标签