Question

我在数据库中有几个表格。我想找到哪一栏(表格中)没有任何数值(一栏中所有民族解放军)。下面的一个例子是,结果应当

TestTable1 --> Var2
TestTable2 --> Variable1

我对如何制造这种疑问没有任何想法。你们的帮助最受赞赏!

--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

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

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:

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

如果表格数量庞大,你可以简单地为所有表格提出询问。所有表格的清单载于<编码>sys.tables。

Answer 2

在此,我写了一封信,用两步手法进行:

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

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 ALL statement. It will look like this:

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.

Answer 3

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

THe 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...

原始代码从版权下修改,只使用部分。

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

这样做太有益,不能被置于少数制度之下——特别是如果你刚刚继承一个遗产数据库,并且不知道你可以减少或忽视哪一栏。

/*
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

这里有一张标语,即对于在座标数据库中的所有非豁免表格(或数据库中所有具体表格),标明了所有联合国利比里亚特派团列队。你们选择的方案拟订语言也可以使用同样的技术,假定它能够与一个Kingk数据库交谈。

#!/bin/bash

function help {
    cat <<EOF
Syntax: $0 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 [ "$1" ]
do case "$1" in
      -h | --help | "" ) help
           exit
           ;;
      -v | --verbose ) VERBOSE=1
           shift
           ;;
      * ) break
      ;;
  esac
done

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

db="$1"
shift

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

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

# Global: db
function nullcolumns {
  local table="$1"
  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

Answer 6

DROP TABLE IF EXISTS #null_check
CREATE TABLE   #null_check (TableName VARCHAR(30), ColumnName varchar(100), Percent_Null decimal)

--columns with 1 are all NULL, 0 has some populated values
--Below generates 1 sql statement per column for every column and let rip.


SELECT CONCAT( INSERT INTO #NULL_CHECK SELECT    , t.name,    ,   ,c.name,    ,sum(case when ISNULL(CONVERT(VARCHAR(10), ,c.name,  ),   ,   ) IN (   ,   ,  *  ) THEN 1.00 else 0.00 end)/count(*) percent_NULL FROM  ,t.name) FROM sys.tables t JOIN sys.columns c ON t.object_id = c.object_id


--See returns
SELECT * FROM #null_check

友情链接