1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82
| DECLARE @column_statistics TABLE
(
range_hi_key varchar(max)
, range_rows bigint
, eq_rows bigint
, distinct_range_rows bigint
, avg_range_rows bigint
)
DECLARE @table_statistics TABLE
(
table_name sysname
, table_row_count bigint
, column_name sysname
, stat_name sysname
, null_row_count bigint
)
INSERT INTO @table_statistics
(
table_name
, table_row_count
, column_name
, stat_name
)
SELECT T.name AS table_name
, PS.row_count
, C.name AS column_name
, S.name AS stat_name
FROM sys.stats AS S
INNER JOIN sys.stats_columns AS SC
ON S.object_id = SC.object_id
AND S.stats_id = SC.stats_id
INNER JOIN sys.columns AS C
ON C.object_id = SC.object_id
AND C.column_id = SC.column_id
INNER JOIN sys.tables AS T
ON T.object_id = C.object_id
INNER JOIN sys.dm_db_partition_stats AS PS
ON PS.object_id = T.object_id
WHERE PS.index_id BETWEEN 0 AND 1
AND S.name LIKE '!_WA!_Sys!_%' ESCAPE '!'
DECLARE @table_name sysname
, @stat_name sysname
, @sql varchar(1024)
, @eq_rows bigint
WHILE EXISTS
(
SELECT *
FROM @table_statistics
WHERE null_row_count IS NULL
)
BEGIN
SELECT TOP (1) @table_name = table_name
, @stat_name = stat_name
, @eq_rows = NULL
FROM @table_statistics
WHERE null_row_count IS NULL
SELECT @sql = 'DBCC SHOW_STATISTICS (' + @table_name + ', ' + @stat_name + ') WITH HISTOGRAM'
PRINT @sql
INSERT INTO @column_statistics
EXEC (@sql)
SELECT @eq_rows = eq_rows
FROM @column_statistics
WHERE range_hi_key IS NULL
UPDATE @table_statistics
SET null_row_count = ISNULL(@eq_rows, 0)
WHERE stat_name = @stat_name
DELETE FROM @column_statistics
END
SELECT table_name
, column_name
, CAST((CAST(null_row_count AS decimal(21, 2)) / CASE table_row_count WHEN 0 THEN 1 ELSE table_row_count END) * 100 AS decimal(5,2)) AS null_ratio
FROM @table_statistics |
Partager