在表中查找 SQL Server 中的非空列 [英] Find the non null columns in SQL Server in a table

问题描述

我已经阅读了很多答案，但它们都是针对 PL/SQL 或 Oracle 的，我找不到针对 Microsoft SQL-Server 的任何内容.

我的桌子:

CREATE TABLE StudentScore(Student_ID INT PRIMARY KEY，Student_Name NVARCHAR (50),Student_Score INT)走INSERT INTO StudentScore VALUES (1,'Ali', NULL)INSERT INTO StudentScore VALUES (2,'Zaid', 770)INSERT INTO StudentScore VALUES (3,'Mohd', 1140)插入 StudentScore 值 (4,NULL, 770)INSERT INTO StudentScore VALUES (5,'John', 1240)INSERT INTO StudentScore VALUES (6,'Mike', 1140)INSERT INTO StudentScore VALUES (7,'Goerge', NULL)

1. 如何查找所有非空列的名称.
2. 返回只包含非空列的表

根据评论进行

我知道 Information_schema 的 IS_NULLABLE 属性.但仅仅因为一列允许空值并不意味着它实际上会有空值.如何找出实际具有空值的列.

我正在寻找一些与 Microsoft SQL-SERVER 等效的 num_nulls.

解决方案

您可以通过发布:

SELECTFORMATMESSAGE('SELECT col = ''%s.%s.%s'' FROM %s.%s HAVING COUNT(*) != COUNT(%s)',QUOTENAME(TABLE_SCHEMA)，QUOTENAME(TABLE_NAME)，QUOTENAME(COLUMN_NAME)，QUOTENAME(TABLE_SCHEMA)，QUOTENAME(TABLE_NAME)，QUOTENAME(COLUMN_NAME))来自 INFORMATION_SCHEMA.COLUMNSWHERE IS_NULLABLE = '是';

db<>小提琴演示

它将生成用于检查单个列的脚本.

HAVING COUNT(*) != COUNT(col_name) -- 这意味着该列至少包含一个 NULLHAVING COUNT(col_name) = 0 AND COUNT(*) != 0 -- 这意味着列中的所有值都是 NULL

这种方法可以通过使用 STRING_AGG 来优化每个表的单个查询，并且使用动态 SQL 可以避免复制查询的需要.

完全烘焙的解决方案:

DECLARE @sql NVARCHAR(MAX);SELECT @sql = STRING_AGG(FORMATMESSAGE('SELECT table_schema = ''%s'',table_name = ''%s'',table_col_name = ''%s'',row_num = COUNT(*),row_num_non_nulls = COUNT(%s),row_num_nulls = COUNT(*) - COUNT(%s)从 %s.%s',QUOTENAME(TABLE_SCHEMA)，QUOTENAME(TABLE_NAME)，QUOTENAME(COLUMN_NAME)，QUOTENAME(COLUMN_NAME)，QUOTENAME(COLUMN_NAME)，QUOTENAME(TABLE_SCHEMA)，QUOTENAME(TABLE_NAME)，QUOTENAME(COLUMN_NAME)), 'UNION ALL' + CHAR(13)) WITHIN GROUP (ORDER BY TABLE_SCHEMA, TABLE_NAME)来自 INFORMATION_SCHEMA.COLUMNSWHERE IS_NULLABLE = '是'AND TABLE_NAME = ?-- 按表名过滤AND TABLE_SCHEMA = ?;-- 按模式名称过滤选择@sql;执行(@sql)；

db<>小提琴演示

输出:

+---------------+-----------------+------------------+-----------+------------+---------------+|table_schema |表名|table_col_name |行数 |row_num_non_nulls |row_num_nulls |+--------------+--------------+-----------------+------------+------------+---------------+|[dbo] |[学生成绩] |[学生姓名] |7 |6 |1 ||[dbo] |[学生成绩] |[Student_Score] |7 |5 |2 |+--------------+--------------+-----------------+------------+------------+---------------+

I have read many answers but they are all for PL/SQL or Oracle, I could not find anything for Microsoft SQL-Server.

My table :

CREATE TABLE StudentScore
(
  Student_ID INT PRIMARY KEY,
  Student_Name NVARCHAR (50),
  Student_Score INT
) 

GO

INSERT INTO StudentScore VALUES (1,'Ali', NULL)
INSERT INTO StudentScore VALUES (2,'Zaid', 770)
INSERT INTO StudentScore VALUES (3,'Mohd', 1140)
INSERT INTO StudentScore VALUES (4,NULL, 770)
INSERT INTO StudentScore VALUES (5,'John', 1240)
INSERT INTO StudentScore VALUES (6,'Mike', 1140)
INSERT INTO StudentScore VALUES (7,'Goerge', NULL)

1. How to find the names of all the non-null columns.
2. Return table containing only non null columns

EDIT based on comments:

I am aware of IS_NULLABLE attribute of Information_schema . But just because a column allows null values does not mean it will actually have null values. How to find out columns which actually have null values.

I am looking for some num_nulls equivalent for microsoft SQL-SERVER.

解决方案

You could achieve it by issuing:

SELECT 
  FORMATMESSAGE('SELECT col = ''%s.%s.%s'' FROM %s.%s HAVING COUNT(*) != COUNT(%s)', 
     QUOTENAME(TABLE_SCHEMA),
     QUOTENAME(TABLE_NAME),
     QUOTENAME(COLUMN_NAME),
     QUOTENAME(TABLE_SCHEMA),
     QUOTENAME(TABLE_NAME),
     QUOTENAME(COLUMN_NAME)
  )
FROM INFORMATION_SCHEMA.COLUMNS
WHERE IS_NULLABLE = 'YES';

db<>fiddle demo

It will generate script for checking individual column.

HAVING COUNT(*) != COUNT(col_name) -- it means that column contains at least single NULL

HAVING COUNT(col_name) = 0 AND COUNT(*) != 0 -- it means all values in columns are NULL

This approach could be polished with using STRING_AGG to get single query per table and with dynamic SQL you could avoid the need of copying the query.

EDIT:

Fully baked-solution:

DECLARE @sql NVARCHAR(MAX);

SELECT @sql = STRING_AGG(
  FORMATMESSAGE('SELECT table_schema = ''%s''
                        ,table_name = ''%s''
                        ,table_col_name = ''%s'' 
                        ,row_num = COUNT(*)
                        ,row_num_non_nulls = COUNT(%s)
                        ,row_num_nulls = COUNT(*) - COUNT(%s)
                 FROM %s.%s', 
     QUOTENAME(TABLE_SCHEMA),
     QUOTENAME(TABLE_NAME),
     QUOTENAME(COLUMN_NAME),
     QUOTENAME(COLUMN_NAME),
     QUOTENAME(COLUMN_NAME),
     QUOTENAME(TABLE_SCHEMA),
     QUOTENAME(TABLE_NAME),
     QUOTENAME(COLUMN_NAME)), ' UNION ALL' + CHAR(13)
               ) WITHIN GROUP(ORDER BY TABLE_SCHEMA, TABLE_NAME)

FROM INFORMATION_SCHEMA.COLUMNS
WHERE IS_NULLABLE = 'YES'
  AND TABLE_NAME = ?        -- filter by table name
  AND TABLE_SCHEMA = ?;     -- filter by schema name

SELECT @sql;
EXEC(@sql);

db<>fiddle demo

Output:

+---------------+-----------------+------------------+----------+--------------------+---------------+
| table_schema  |   table_name    | table_col_name   | row_num  | row_num_non_nulls  | row_num_nulls |
+---------------+-----------------+------------------+----------+--------------------+---------------+
| [dbo]         | [StudentScore]  | [Student_Name]   |       7  |                 6  |             1 |
| [dbo]         | [StudentScore]  | [Student_Score]  |       7  |                 5  |             2 |
+---------------+-----------------+------------------+----------+--------------------+---------------+    

这篇关于在表中查找 SQL Server 中的非空列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！