我正在尝试编写一个脚本,该脚本将显示每列中的非空值的数量以及表中的总行数.
我找到了几种方法来做到这一点：
SELECT sum(case null_column为null然后是1 else 0)“Null Values”,
sum(case null_column null时然后0 else 1)“Non-Null Values”
来自my_table;
和
SELECT count(*)FROM my_table WHERE my_column IS NULL
UNION ALL
SELECT count(*)FROM my_table WHERE my_column IS NOT NULL
但这些要求我手动输入每个列名.有没有办法为每个列执行此操作而不列出它们？
解决方法
正如保罗所说,但这是一个例子：
- DECLARE @TableName VARCHAR(512) = 'invoiceTbl';
- DECLARE @sql VARCHAR(1024);
- WITH sqlText AS (
- SELECT
- ROW_NUMBER() OVER (ORDER BY c.Name) AS RowNum,'SELECT ''' + c.name + ''',SUM(CASE WHEN ' + c.Name + ' IS NULL THEN 1 ELSE 0 END) AS NullValues FROM ' + @TableName AS sqlRow
- FROM
- sys.tables t
- INNER JOIN sys.columns c ON c.object_id = t.object_id
- WHERE
- t.name = @TableName),Recur AS (
- SELECT
- RowNum,CONVERT(VARCHAR(MAX),sqlRow) AS sqlRow
- FROM
- sqlText
- WHERE
- RowNum = 1
- UNION ALL
- SELECT
- t.RowNum,r.sqlRow + ' UNION ALL ' + t.sqlRow)
- FROM
- sqlText t
- INNER JOIN Recur r ON t.RowNum = r.RowNum + 1
- )
- SELECT @sql = sqlRow FROM Recur WHERE RowNum = (SELECT MAX(RowNum) FROM Recur);
- EXEC(@sql);
