这是一团糟,但它有效:
DECLARE @SQL nvarchar(MAX),
@CRLF nchar(2) = NCHAR(13) + NCHAR(10);
CREATE TABLE #NullCounts (SchemaName sysname,
TableName sysname,
ColumnName sysname,
NULLCount bigint);
DECLARE @Delimiter nchar(3) = ',' +@CRLF;
SET @SQL = STUFF((SELECT @CRLF + @CRLF +
N'WITH Counts AS(' + @CRLF +
N' SELECT N' + QUOTENAME(s.[name],'''') +N' AS SchemaName,' + @CRLF +
N' N' + QUOTENAME(t.[name],'''') +N' AS TableName,' + @CRLF +
STRING_AGG(N' COUNT_BIG(CASE WHEN ' + QUOTENAME(c.[name]) + N' IS NULL THEN 1 END) AS ' + QUOTENAME(c.[name]),@Delimiter) WITHIN GROUP(ORDER BY c.column_id) + @CRLF +
N' FROM ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) + N' T)' + @CRLF +
N'INSERT INTO #NullCounts(SchemaName, TableName, ColumnName, NULLCount)' + @CRLF +
N'SELECT SchemaName,' + @CRLF +
N' TableName,' + @CRLF +
N' V.ColumnName,' + @CRLF +
N' V.NULLCount' + @CRLF +
N'FROM Counts C' + @CRLF +
N' CROSS APPLY (VALUES' +
STUFF(STRING_AGG(N' (N' + QUOTENAME(c.[name], '''') + N', C.' + QUOTENAME(c.[name]) + N')',@Delimiter) WITHIN GROUP (ORDER BY c.column_id),1,24,N'') + N')V(ColumnName,NULLCount);'
FROM sys.schemas s
JOIN sys.tables t ON s.schema_id = t.schema_id
JOIN sys.columns c ON t.object_id = c.object_id
GROUP BY s.[name], t.[name]
FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,4,N'');
--PRINT @SQL; --This is gunna be way longer than 4,000 characters, so you'll want SELECT
EXEC sys.sp_executesql @SQL;
GO
SELECT *
FROM #NullCounts
ORDER BY SchemaName,
TableName,
ColumnName;
GO
DROP TABLE #NullCounts;
是的,我混合STRING_AGG
and FOR XML PATH
,是的,它有点碍眼,但是打印的(选定的)SQL 会生成一些非常好的语句。见下文:
WITH Counts AS(
SELECT N'dbo' AS SchemaName,
N'PerformanceTest' AS TableName,
COUNT_BIG(CASE WHEN TestID IS NULL THEN 1 END) AS [TestID],
COUNT_BIG(CASE WHEN TestTarget IS NULL THEN 1 END) AS [TestTarget],
COUNT_BIG(CASE WHEN TestName IS NULL THEN 1 END) AS [TestName],
COUNT_BIG(CASE WHEN TimeStart IS NULL THEN 1 END) AS [TimeStart],
COUNT_BIG(CASE WHEN TimeEnd IS NULL THEN 1 END) AS [TimeEnd],
COUNT_BIG(CASE WHEN TimeTaken_ms IS NULL THEN 1 END) AS [TimeTaken_ms],
COUNT_BIG(CASE WHEN TotalRows IS NULL THEN 1 END) AS [TotalRows],
COUNT_BIG(CASE WHEN RowSets IS NULL THEN 1 END) AS [RowSets],
COUNT_BIG(CASE WHEN AvgRowsPerSet IS NULL THEN 1 END) AS [AvgRowsPerSet]
FROM [dbo].[PerformanceTest] T)
INSERT INTO #NullCounts(SchemaName, TableName, ColumnName, NULLCount)
SELECT SchemaName,
TableName,
V.ColumnName,
V.NULLCount
FROM Counts C
CROSS APPLY (VALUES(N'TestID', C.[TestID]),
(N'TestTarget', C.[TestTarget]),
(N'TestName', C.[TestName]),
(N'TimeStart', C.[TimeStart]),
(N'TimeEnd', C.[TimeEnd]),
(N'TimeTaken_ms', C.[TimeTaken_ms]),
(N'TotalRows', C.[TotalRows]),
(N'RowSets', C.[RowSets]),
(N'AvgRowsPerSet', C.[AvgRowsPerSet]))V(ColumnName,NULLCount);
WITH Counts AS(
SELECT N'dbo' AS SchemaName,
N'someTable' AS TableName,
COUNT_BIG(CASE WHEN id IS NULL THEN 1 END) AS [id],
COUNT_BIG(CASE WHEN SomeCol IS NULL THEN 1 END) AS [SomeCol]
FROM [dbo].[someTable] T)
INSERT INTO #NullCounts(SchemaName, TableName, ColumnName, NULLCount)
SELECT SchemaName,
TableName,
V.ColumnName,
V.NULLCount
FROM Counts C
CROSS APPLY (VALUES(N'id', C.[id]),
(N'SomeCol', C.[SomeCol]))V(ColumnName,NULLCount);
是的,我真的花了最后 45 分钟写下所有这些……
老实说,这不是入门级的,如果你不理解它,你就不应该使用它;而且,我非常怀疑您是否会找到一个不同的入门级解决方案,并且性能与此一样。A CURSOR
,例如,虽然可能更容易理解,但是这样做会很慢.
Caveat:如果您的数据库中有任何已弃用的数据类型(即text
)这将会失败。如果是这种情况,您需要确保从查询中消除它们WHERE
。但是,我建议您修复数据类型(text
,例如,已被弃用 15 年)。