Saturday, February 12, 2011

How to get the statistics existing on a column, if any?

I want to check in Transact SQL if a specific column in a table has statistics and if so to get them all.

  • This query should do it.
    I use it in a stored proc that browse the DB to find stats.
    Works in SQL Server 2005 and probably older version as well.

    SELECT S.NAME
    FROM   SYS.OBJECTS AS O
           INNER JOIN SYS.STATS AS S
             ON O.OBJECT_ID = S.OBJECT_ID
           INNER JOIN SYS.STATS_COLUMNS AS SC
             ON SC.OBJECT_ID = S.OBJECT_ID
                AND S.STATS_ID = SC.STATS_ID
    WHERE  (O.OBJECT_ID = OBJECT_ID('MyTable','local'))
           AND (O.TYPE IN ('U'))
           AND (INDEXPROPERTY(S.OBJECT_ID,S.NAME,'IsStatistics') = 1)  /* only stats */
           AND (COL_NAME(SC.OBJECT_ID,SC.COLUMN_ID) = 'MyColumn')
    
    From François

0 comments:

Post a Comment