SQL Server – Statistics – Object level

Preface

Statistics are very important to the SQL Server Engine.

They inform the Query Engine in the areas of Index Selections.

Code

The query below list statistics columns and corresponding indexes when one exists.


; with cteStatColumn
(
	  [object_id]
	, [stats_id]
	, [statName]
	, [statColumns]
	, [statDate]
	, auto_created
	, user_created

)
as
(

  select 
	  tblStat.[object_id]
	, tblStat.[stats_id]
	, [Statname] 
	  = tblStat.name
	, [StatColumns]
		= SUBSTRING(
 	 		    (
  			       SELECT ', ' +  tblC.Name as [text()]
			       FROM sys.stats_columns tblSC
					INNER JOIN sys.columns tblC
				  	  ON  tblSC.object_id = tblC.object_id
					  AND tblSC.column_id = tblC.column_id

																	
				WHERE tblStat.object_id = tblSC.object_id
				AND   tblStat.stats_id = tblSC.stats_id

				ORDER BY tblSC.stats_column_id
				FOR XML Path('')
			   )
			  , 2
			  , 10000
			) 

	, STATS_DATE
	    (
	         tblStat.object_id
	       , tblStat.stats_id
	    )

	, auto_created

	, user_created

   from   sys.stats tblStat


)
select 

      [schema]
	=object_schema_name(tblStat.object_id)

    , [object]
	=object_name(tblStat.object_id)

   , [statName]
	= tblStat.statName

   , [statColumns]
	= tblStat.statColumns

   , [statDate]
	= tblStat.[statDate]

   , auto_created

   , user_created

   , indexName 
	=tblI.name

   , [indexIsDisabled]
	= isNull(tblI.is_disabled, 0)

from   cteStatColumn tblStat

	  left outer join sys.indexes tblI

	     on  tblStat.object_id = tblI.object_id
	     and tblStat.stats_id = tblI.index_id


order by
	  object_schema_name(tblStat.object_id)

	, object_name(tblStat.object_id)

	, [StatColumns]


Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s