Check the existence of statistic with same columns before creating a statistic on a SQL table

Question

I need to create some statistics on a table with different combination of columns. But there may be already some statistic exist with same combination of columns. So, Before creating a statistic with a combination of columns, I want to check if there exists any statistic with same combination of columns. If exist then I will not create the statistic and if not exist then only I will create the statistic.

For example create a table and a statistic on this table as following:

CREATE TABLE Gift
(
     Gift_Id INTEGER IDENTITY (1,1) PRIMARY KEY,
     Person_Id INTEGER,
     Event_Id INTEGER,
     Agent_Id INTEGER,
     Fund_Id INTEGER,
     Amount FLOAT
)

CREATE STATISTICS [Stats1_1_2_3] 
    ON [dbo].[Gift]([Gift_Id], [Person_Id], [Event_Id])

So we have a table Gift and a statistic with columns Gift_ID, Person_Id and Event_Id.

Now If I create another statistic as following:

CREATE STATISTICS [Stats2_1_2_3] 
    ON [dbo].[Gift]([Gift_Id], [Person_Id], [Event_Id])

See later statistic is duplicate of first statistic (With same columns).

So, to avoid the duplication, I need to check if there exists any statistic with same columns.

Is there any way to do this?

Please help

Answer 1

The below query will check the sys.stats,sys.stats_columns and sys.columns table to find all statistics for the given table and get each column for each statistic. The grouping and counting is to check if one statistic exists that refers to all columns.
Note that the query mentions explicitly the table name, the column names and the number of columns that is being checked. The updated query also checks if ONLY the columns listed are present (counting = 3) and ALL the columns listed are present (counting3 = 3). Use for other statistics will involve modifications for the CASE statement and the '3' values in the last WHERE line.

SELECT * FROM (
  SELECT s.name AS statistics_name,
      count(*) as counting,
      sum(case
          when c.name = 'Gift_Id' then 1
          when c.name = 'Person_Id' then 1
          when c.name = 'Event_Id' then 1
          else 0
      end) as counting3
  FROM sys.stats AS s  
  INNER JOIN sys.stats_columns AS sc   
  ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id  
  INNER JOIN sys.columns AS c   
  ON sc.object_id = c.object_id AND c.column_id = sc.column_id  
  WHERE s.object_id = OBJECT_ID('Gift')
  Group by s.name) T
WHERE T.COUNTING = 3 AND T.COUNTING3 = 3;

Updated SQLFiddle for the above