How To Find Statistics For Table \ Column In SQL Server Database


/**** All Statistics for particular Table / Column*******/
          Use database_name
          GO
          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('SCHEMA.Table_Name'))
       AND (O.TYPE IN ('U'))
       AND (INDEXPROPERTY(S.OBJECT_ID,S.NAME,'IsStatistics') = 1)  
       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
          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('dbo.a'))
       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) = 'b')




No comments:

Post a Comment