查找SQL Server中是否有包含空值的列(Find if any columns that contain null in SQL Server)

查找SQL Server中是否有包含空值的列(Find if any columns that contain null in SQL Server)

我是SQL新手，我在解决这个问题时遇到了一些困难。

我想编写select查询，它返回所有那些有空值的行。

我在桌面上有50多列，并且可以添加一些额外的列，因为这样我就很难写出条件。

我们可以使用AFAIK is null但我不想重复它那么多列。

请帮我解决它。 让我知道是否需要任何其他信息。

I new to SQL, I am having some difficulty solving this problem.

I want to write the select query which returns all those rows which have the null value in it.

I have more than 50 columns on the table and can be added some extra column it and because of that I getting difficult to write where condition.

AFAIK we can use is null but I don't want to repeat it for that much columns.

Please help me solve it. Let me know if any additional information required.

最满意答案

你可以试试这个动态的SQL查询。 如果任何列包含空值，则此查询将返回行。

DECLARE @tb NVARCHAR(255) = N'dbo.[tablename]'; DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM ' + @tb + ' WHERE 1 = 0'; SELECT @sql += N' OR ' + QUOTENAME(name) + ' IS NULL' FROM sys.columns WHERE [object_id] = OBJECT_ID(@tb) AND [is_nullable]=1; EXEC sp_executesql @sql;

You can try this dynamic SQL query. This query will return row if any column contain null value.

DECLARE @tb NVARCHAR(255) = N'dbo.[tablename]'; DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM ' + @tb + ' WHERE 1 = 0'; SELECT @sql += N' OR ' + QUOTENAME(name) + ' IS NULL' FROM sys.columns WHERE [object_id] = OBJECT_ID(@tb) AND [is_nullable]=1; EXEC sp_executesql @sql;