AzureSynapseDedicatedSQLPool统计指定表中各字段的空值、空字符串或零值比例

-- 创建临时表存储结果 CREATE TABLE #Results ( DatabaseName NVARCHAR(128), TableName NVARCHAR(128), ColumnName NVARCHAR(128), DataType NVARCHAR(128), NullOrEmptyCount INT, TotalRows INT, Percentage DECIMAL(10,2) ); DECLARE @db_name SYSNAME = DB_NAME(); -- 获取当前数据库名 DECLARE @table_name SYSNAME; DECLARE @column_name SYSNAME; DECLARE @data_type SYSNAME; DECLARE @sql NVARCHAR(MAX); -- 定义表游标 DECLARE table_cursor CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = @db_name; OPEN table_cursor; FETCH NEXT FROM table_cursor INTO @table_name; WHILE @@FETCH_STATUS = 0 BEGIN -- 定义列游标 DECLARE column_cursor CURSOR FOR SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table_name AND TABLE_CATALOG = @db_name; OPEN column_cursor; FETCH NEXT FROM column_cursor INTO @column_name, @data_type; WHILE @@FETCH_STATUS = 0 BEGIN -- 构建动态SQL SET @sql = N' INSERT INTO #Results SELECT ''' + @db_name + ''' AS DatabaseName, ''' + @table_name + ''' AS TableName, ''' + @column_name + ''' AS ColumnName, ''' + @data_type + ''' AS DataType, '; -- 根据数据类型处理逻辑 IF @data_type IN ('varchar', 'nvarchar', 'char', 'nchar', 'text') BEGIN SET @sql += N' SUM(CASE WHEN ISNULL(TRIM([' + @column_name + ']), '''') = '''' THEN 1 ELSE 0 END),'; END ELSE IF @data_type IN ('int', 'bigint', 'smallint', 'tinyint', 'decimal', 'numeric', 'float', 'real') BEGIN SET @sql += N' SUM(CASE WHEN ISNULL([' + @column_name + '], 0) = 0 THEN 1 ELSE 0 END),'; END ELSE BEGIN SET @sql += N' SUM(CASE WHEN [' + @column_name + '] IS NULL THEN 1 ELSE 0 END),'; END -- 添加行数和百分比计算 SET @sql += N' COUNT(*) AS TotalRows, ROUND( (SUM(CASE WHEN '; IF @data_type IN ('varchar', 'nvarchar', 'char', 'nchar', 'text') SET @sql += N'ISNULL(TRIM([' + @column_name + ']), '''') = '''' ' ELSE IF @data_type IN ('int', 'bigint', 'smallint', 'tinyint', 'decimal', 'numeric', 'float', 'real') SET @sql += N'ISNULL([' + @column_name + '], 0) = 0 ' ELSE SET @sql += N'[' + @column_name + '] IS NULL '; SET @sql += N'THEN 1 ELSE 0 END) * 100.0) / NULLIF(COUNT(*), 0), 2 ) FROM ' + QUOTENAME(@table_name) + ';'; -- 执行动态SQL EXEC sp_executesql @sql; FETCH NEXT FROM column_cursor INTO @column_name, @data_type; END CLOSE column_cursor; DEALLOCATE column_cursor; FETCH NEXT FROM table_cursor INTO @table_name; END CLOSE table_cursor; DEALLOCATE table_cursor; -- 查询最终结果 SELECT DatabaseName, TableName, ColumnName, DataType, NullOrEmptyCount, TotalRows, Percentage FROM #Results ORDER BY TableName, ColumnName; -- 清理临时表 DROP TABLE #Results;

代码说明：

临时表创建：使用#Results临时表存储最终结果集

游标遍历：

外层游标遍历所有用户表内层游标遍历每个表的所有列

动态SQL构建：

根据列数据类型生成不同的统计逻辑字符串类型：使用TRIM()和空字符串判断数值类型：使用0值判断其他类型：直接判断NULL值

安全处理：

使用QUOTENAME()防止SQL注入使用NULLIF()处理除零错误

结果输出：

包含数据库名、表名、列名、数据类型统计空值/空字符串数量显示总行数和百分比

执行结果示例：

DatabaseNameTableNameColumnNameDataTypeNullOrEmptyCountTotalRowsPercentageMyDBCustomerFirstNamevarchar125100001.25MyDBCustomerAgeint23001000023.00MyDBOrderOrderDatedatetime4550000.90

注意事项：

需要具有访问系统视图的权限对大型表执行COUNT(*)可能影响性能确保在正确的数据库上下文中执行结果百分比保留两位小数处理text类型字段时可能需要考虑性能影响