Query To Find Statistics For Specific Column In SQL Server Database

         

/**** All Statistics for particular Column*******/

          Use database_name

          GO

          SELECT S.NAME  ,Object_name(S.OBJECT_ID) as TABLE_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.TYPE IN ('U'))

       AND (INDEXPROPERTY(S.OBJECT_ID,S.NAME,'IsStatistics') = 1)  /* only stats */

       AND (COL_NAME(SC.OBJECT_ID,SC.COLUMN_ID) = 'Column_Name')

/***********************************************/ 

       

          Example:

          Create database A

          GO

          use A

          GO

          Create table a(a int , b int)

          GO

          insert into a values (1,1)

          CREATE STATISTICS [stats_a_a] ON [dbo].[a]([a])

          CREATE STATISTICS [stats_a_b] ON [dbo].[a]([b])

          GO

         

          -- All Statistics For particular column

         

          SELECT S.NAME ,Object_name(S.OBJECT_ID) as TABLE_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.TYPE IN ('U'))

       AND (INDEXPROPERTY(S.OBJECT_ID,S.NAME,'IsStatistics') = 1)  /* only stats */

       AND (COL_NAME(SC.OBJECT_ID,SC.COLUMN_ID) = 'b')