检查具有所有空值的列 [英] check the column which having all null values

问题描述

我们有一个名为TEST的表格。有4列（Col1，Col2，Col3，Col4，.........，coln），有以下数据 -



1，测试，考试，在线...........

NULL，NULL，NULL，NULL

2，NULL，练习，NULL

NULL，NULL，NULL，NULL

NULL，NULL，NULL，NULL

NULL，NULL，NULL，NULL



如何检查具有所有空值的列？

感谢您的回复，

但我不知道有多少列是那里。

We have a table named "TEST" with 4 columns (Col1, Col2, Col3, Col4,.........,coln) with following data -

1, Test, Exam, Online ...........
NULL, NULL, NULL, NULL
2, NULL, Practice, NULL
NULL, NULL, NULL, NULL
NULL, NULL, NULL, NULL
NULL, NULL, NULL, NULL

How can i check the column which having all null values?
Thank you for all reply,
but i don''t know how many number of columns are there.

推荐答案

你好，



使用 COALESCE（）如下

Hi ,

Use COALESCE() Function like as follows

CREATE TABLE #Test (Col1 INT, Col2 INT, Col3 INT, Col4 INT)

INSERT INTO #Test(Col1,Col2,COl3, Col4) VALUES(NULL,1,NULL,NULL),(NULL,NULL,NULL,NULL)
INSERT INTO #Test(Col1,Col2,COl3, Col4) VALUES(NULL,NULL,2,NULL),(NULL,NULL,NULL,NULL)
INSERT INTO #Test(Col1,Col2,COl3, Col4) VALUES(NULL,NULL,NULL,3),(NULL,NULL,NULL,NULL)

SELECT Col1,Col2,Col3, Col4 FROM #Test WHERE COALESCE(Col1, Col2, Col3, Col4) IS NULL

问候，

GVPrabu

Regards,
GVPrabu

对于未知数o列，请尝试：

For unknow number o columns, try this:

USE DataBaseName;

DECLARE @cols NVARCHAR(2000)
DECLARE @tblName NVARCHAR(30)
DECLARE @sql NVARCHAR(MAX)

SET @tblName = 'TableName'
SET @cols = STUFF((SELECT DISTINCT '],[' + syscolumns.name 
					FROM sysobjects INNER JOIN syscolumns  ON sysobjects.id = syscolumns.id  
					WHERE (sysobjects.xtype = 'u' and sysobjects.name = @tblName)
					--ORDER BY '],[' + syscolumns.colid 
			FOR XML PATH('')),1,2,'') + ']'

SELECT @cols AS [columns]


SET @sql = 'SELECT ' + @cols + ' ' +
		'FROM ' + @tblName + ' ' +
		'WHERE COALESCE(' + @cols + ') IS NULL'
EXEC (@sql)

风险：为列名保留的字符串长度 - 存储在 @cols 变量中 - 可以超过声明值（2000）！

RISK: Length of string reserved for column names - stored in @cols variable - can exceed declared value (2000)!

这篇关于检查具有所有空值的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！