I was just starting to work on a post on column statistics, using one of my favorite metadata functions: sys.dm_db_stats_properties(), when I realized something was missing.
The function requires a stats_id value, which is available from sys.stats. However, sys.stats does not show the column names that each statistics object is attached to. So how can I know which stats_id is the one I want?
So I looked at sp_helpstats, and it shows the column names, but NOT the stats_id! I spent some time trying to figure out how to translate the stats_name into the column name, so that I could just use sys.stats. For example, if you see a stats_name of _WA_Sys_0000000C_38B96646, the 38B96646 is the HEX representation of the object_id and the 0000000C is the HEX representation of the column_id. I can find the column name with this information, but it’s not quick and easy, especially not to generalize in TSQL code.
What DID turn out to be quick and easy was to rewrite sp_helpstats so that it returns the stats_id. It turned out to be an incredibly simple change. I just added an additional column to the temp table created in the procedure, and then added that column to the INSERT into the temp table and the SELECT from the temp table to return the final results. The cursor already fetched the stats_id column to use in its work; it just didn’t return it.
Note that my script, which I am attaching, has not been thoroughly tested. It has been tested enough for my use when poking around the statistics, and using the sys.dm_db_stats_properties() function.
Perhaps some of you will find this useful. And if not, at least I’ll know where to find the script next time I need it!