sqlserver 计算数据列统计信息

sqlserver版本 2008

需求描述：

oracle 系统表 user_tab_cols 提供数据列的各种统计信息，包括：
低值（low_value），高值（high_value），不同值的数量（num_distinct），空值数量（num_nulls）

sqlserver系统表 sys.columns 中未提供相关内容，只能通过如下方式单独查看每一个表的状态:
DBCC SHOW_STATISTICS (‘数据表名’,‘列名’)

如果需要查看当前数据库中所有表的数据列状态，通过 DBCC SHOW_STATISTICS 过于繁琐，且输出信息不直观；

本文讲解如何实现类似 oracle 数据列统计信息记录。

实现过程

-- =============================================
-- 1、新增 统计结果存储表
-- =============================================
create table std_tab_col_stats(
	table_name varchar (50) /*表名*/
	, column_name varchar (50) /*列名*/
	, low_value varchar (100) /*低值*/
	, high_value varchar (100) /*高值*/
	, num_distinct int /*不同值数量*/
	, num_nulls int /*null值数量*/
	, has_data int /*是否有非null值*/
	, last_analyzed datetime /*最后统计日期*/
, constraint pk_std_tab_col_stats primary key(table_name,column_name))
GO 

-- =============================================
-- 2、创建存储过程
-- DROP PROCEDURE pro_table_stats;
-- GO
-- =============================================
CREATE PROCEDURE pro_table_stats @table_name varchar(50)
AS
	DECLARE @sql_str VARCHAR(8000), @column_name VARCHAR(100);

	/*检索表列信息*/
	DECLARE cols_cursor CURSOR for
	SELECT name AS column_name FROM sys.columns WHERE object_id = object_id(@table_name)
	AND user_type_id NOT IN(SELECT xusertype FROM sys.systypes WHERE name = 'image');
	
	OPEN cols_cursor;
	FETCH next FROM cols_cursor INTO @column_name;
	WHILE @@FETCH_STATUS = 0
	BEGIN	
		/*移除 已有数据列统计信息*/
		DELETE FROM STD_TAB_COL_STATS WHERE table_name = @table_name AND column_name = @column_name;

		/*计算 数据列统计信息*/				
		SET @sql_str='
		INSERT INTO STD_TAB_COL_STATS(table_name, column_name, low_value, high_value, num_distinct, num_nulls, has_data,last_analyzed)
		select '''+@table_name+'''	 as table_name
		, ''' + @column_name + ''' as column_name
		, cast(min(' + @column_name + ') AS VARCHAR(50)) as low_value
		, cast(max(' + @column_name + ') AS VARCHAR(50)) as high_value
		, count(distinct ' + @column_name + ') as num_distinct
		, sum(case when ' + @column_name + ' is null then 1 else 0 end ) as num_nulls
		, case when max(' + @column_name + ') is null then 0 else 1 end as has_data 
		, getdate() as last_analyzed
		from ' +@table_name + ' WITH (nolock)'
		
		EXEC (@sql_str);
		PRINT 'table:' + @table_name + ' col:'+@column_name
		FETCH next FROM cols_cursor into @column_name; 
	END ;
	
	CLOSE cols_cursor;
	DEALLOCATE cols_cursor;
	
	
GO

-- =============================================
-- 调用示例
-- =============================================
EXECUTE pro_table_stats 'DA_GR_JBXX';
GO

-- =============================================
-- 查看输出
-- =============================================
SELECT * FROM STD_TAB_COL_STATS;

输出结果示例：

–END–