Thursday, March 24, 2016

SQL Server - DBCC show_statistics from table_name, column_name

Because auto-generated stats names are cryptic, I end up using this a lot... 

DECLARE @tbl      NVARCHAR(256);
DECLARE @col      NVARCHAR(256);
DECLARE @statName NVARCHAR(256);
DECLARE @sqlText  NVARCHAR(MAX);
SET @tbl = '<table_name>';
SET @col = '<column_name>';
SELECT TOP (1) @statName    = s_stat.name
FROM sys.stats_columns s_statcol
JOIN sys.columns s_col
     ON s_col.[object_id]   = s_statcol.[object_id]
        AND s_col.column_id = s_statcol.column_id
JOIN sys.tables s_tbl
     ON s_tbl.[object_id]   = s_statcol.[object_id]
JOIN sys.stats s_stat
     ON s_stat.[object_id]  = s_statcol.[object_id]
        AND s_stat.stats_id = s_statcol.stats_id
WHERE s_tbl.name     = @tbl
      AND s_col.name = @col;
SET @sqlText = N'DBCC SHOW_STATISTICS(' + @tbl + ',' + @statName + N');';
EXEC (@sqlText);