SQL对sys表中所有表的每个列中的所有空值进行计数

Question

我有以下SQL：

    SET NOCOUNT ON
DECLARE @Schema NVARCHAR(100) = 'dbo'
DECLARE @Table NVARCHAR(100) = NULL
DECLARE @sql NVARCHAR(MAX) =''
IF OBJECT_ID ('tempdb..#Nulls') IS NOT NULL DROP TABLE #Nulls

CREATE TABLE #Nulls (TableName sysname, ColumnName sysname, ColumnPosition int 
, NullCount int , NonNullCount int)

SELECT @sql += 'SELECT  '''+TABLE_NAME+''' AS TableName ,

'''+COLUMN_NAME+''' AS ColumnName,  '''+CONVERT(VARCHAR(5),ORDINAL_POSITION)+'''AS ColumnPosition,     
SUM(CASE WHEN '+COLUMN_NAME+' IS NULL THEN 1 ELSE 0 END) CountNulls ,   
COUNT(' +COLUMN_NAME+') CountnonNulls FROM   
'+QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME)+';'+ CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @Schema AND (@Table IS NULL OR TABLE_NAME = @Table)

INSERT INTO #Nulls 
EXEC sp_executesql @sql

SELECT * 
FROM #Nulls

这将按照我期望的方式对所有null进行计数。但是，我想使用此sql遍历目录中的每个表，例如在

  information.schema.table

有人可以提供执行此操作所需的sql吗，我对使用游标一无所知。

谢谢

Answer 1

无需游标。只需将您设置@Table为NULL

DECLARE @Table NVARCHAR(100) = NULL

然后WHERE从此更改您的条款

WHERE TABLE_SCHEMA = @Schema AND TABLE_NAME = @Table

到 WHERE TABLE_SCHEMA = @Schema AND (@Table IS NULL OR TABLE_NAME = @Table)

所以整个代码…

SET NOCOUNT ON
DECLARE @Schema NVARCHAR(100) = 'dbo'
DECLARE @Table NVARCHAR(100) = NULL
DECLARE @sql NVARCHAR(MAX) =''
IF OBJECT_ID ('tempdb..#Nulls') IS NOT NULL DROP TABLE #Nulls

CREATE TABLE #Nulls (TableName sysname, ColumnName sysname, ColumnPosition int 
, NullCount int , NonNullCount int)

SELECT @sql += 'SELECT  '''+TABLE_NAME+''' AS TableName ,

'''+COLUMN_NAME+''' AS ColumnName,  '''+CONVERT(VARCHAR(5),ORDINAL_POSITION)+'''AS ColumnPosition,     
SUM(CASE WHEN '+COLUMN_NAME+' IS NULL THEN 1 ELSE 0 END) CountNulls ,   
COUNT(' +COLUMN_NAME+') CountnonNulls FROM   
'+QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME)+';'+ CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @Schema AND (@Table IS NULL OR TABLE_NAME = @Table)

INSERT INTO #Nulls 
EXEC sp_executesql @sql

SELECT * 
FROM #Nulls