DataWriter

March 17, 2011

Listing Statistics Columns

Filed under: SQLServerPedia Syndication,T-SQL — Joe Casella @ 10:45 pm
Tags: , ,

As a follow up to yesterday’s post, here’s how to get a list of table statistics using DMVs:

  1. SELECT Table_Name     = SCHEMA_NAME(st.Schema_id) + '.'
  2.                         + OBJECT_NAME(st.object_id)
  3.     , stats_name      = ss.name
  4.     , [stats_columns] = LEFT(SColumns, LEN(Scolumns) 1)
  5.     , ss.user_created
  6.     , ss.auto_created
  7.     , ss.filter_definition
  8.   FROM sys.stats ss
  9.   join sys.tables st
  10.     on ss.object_id = st.object_id
  11. CROSS APPLY
  12.      (SELECT cc.name + ', '
  13.         FROM sys.stats_columns sc
  14.         JOIN sys.columns cc
  15.           on sc.object_id = cc.object_id
  16.          and sc.column_id = cc.column_id
  17.        WHERE ss.object_id = sc.object_id
  18.          and ss.stats_id = sc.stats_id
  19.        ORDER BY SC.stats_column_id
  20.          FOR XML PATH ('') ) cl (SColumns);

As with the last query, substitute sys.objects for sys.tables if you need to capture information about views.

Advertisements

1 Comment »

  1. […] I use APPLY with XML to pivot data into a single field to solve a range of problems. One of my favorites is providing single-row column lists from the various “columns” DMVs. In two earlier posts, I showed how to query DMVs to produce list of the columns in an index or table statistics. […]

    Pingback by T-SQL Tuesday #17–APPLY « DataReader — April 12, 2011 @ 11:07 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

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

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: