在表中查找 SQL Server 中的非空列

输出:+---------------+-----------------+------------------+-----------+------------+---------------+|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)GOINSERT 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)How to find the names of all the non-null columns.Return table containing only non null columnsEDIT 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.COLUMNSWHERE IS_NULLABLE = 'YES';db<>fiddle demoIt will generate script for checking individual column.HAVING COUNT(*) != COUNT(col_name) -- it means that column contains at least single NULLHAVING COUNT(col_name) = 0 AND COUNT(*) != 0 -- it means all values in columns are NULLThis 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.COLUMNSWHERE IS_NULLABLE = 'YES' AND TABLE_NAME = ? -- filter by table name AND TABLE_SCHEMA = ?; -- filter by schema nameSELECT @sql;EXEC(@sql);db<>fiddle demoOutput:+---------------+-----------------+------------------+----------+--------------------+---------------+| 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 中的非空列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！