Home
  Home
Home
Search
Articles
Page Tag-Cloud
  Software
Software Tag-Cloud
Submit Patch
Building from Source
Open Source Definition
  Popular Tags
C++
Source Code
Class
Legacy
Cryptography
  Members
Login
Web-Email
Notable Members
  Official
Our Company
Copyright Information
Software EULA
GPL EULA
LGPL Eula
Pre-Release EULA
Privacy Policy
  Support
Make Contact
 
 
Finding Duplicate SQL Statistics
Without statistics, indexes on SQL tables are nearly useless, but that's no excuse to have a bunch of duplicates sitting around. These two views work in tandem with one-another, the first view (vStatisticsWithColumns) returns each of the statistics in your database with the names of the first 25 columns it covers. The second view (vDuplicateStatistics) returns the names of the statistics that cover the same columns. This does not indicate that the statistic is 100% duplicated, as the stat may have more than 25 columns - but I'll leave that part up to you.

See also: Finding Duplicate SQL Indexes

CREATE VIEW vStatisticsWithColumns AS
SELECT
	tbl.[name] AS TableName,
	idx.[name] AS IndexName,
	INDEX_COL( tbl.[name], idx.indid, 1 ) AS col1,
	INDEX_COL( tbl.[name], idx.indid, 2 ) AS col2,
	INDEX_COL( tbl.[name], idx.indid, 3 ) AS col3,
	INDEX_COL( tbl.[name], idx.indid, 4 ) AS col4,
	INDEX_COL( tbl.[name], idx.indid, 5 ) AS col5,
	INDEX_COL( tbl.[name], idx.indid, 6 ) AS col6,
	INDEX_COL( tbl.[name], idx.indid, 7 ) AS col7,
	INDEX_COL( tbl.[name], idx.indid, 8 ) AS col8,
	INDEX_COL( tbl.[name], idx.indid, 9 ) AS col9,
	INDEX_COL( tbl.[name], idx.indid, 10 ) AS col10,
	INDEX_COL( tbl.[name], idx.indid, 11 ) AS col11,
	INDEX_COL( tbl.[name], idx.indid, 12 ) AS col12,
	INDEX_COL( tbl.[name], idx.indid, 13 ) AS col13,
	INDEX_COL( tbl.[name], idx.indid, 14 ) AS col14,
	INDEX_COL( tbl.[name], idx.indid, 15 ) AS col15,
	INDEX_COL( tbl.[name], idx.indid, 16 ) AS col16,
	INDEX_COL( tbl.[name], idx.indid, 17 ) AS col17,
	INDEX_COL( tbl.[name], idx.indid, 18 ) AS col18,
	INDEX_COL( tbl.[name], idx.indid, 19 ) AS col19,
	INDEX_COL( tbl.[name], idx.indid, 20 ) AS col20,
	INDEX_COL( tbl.[name], idx.indid, 21 ) AS col21,
	INDEX_COL( tbl.[name], idx.indid, 22 ) AS col22,
	INDEX_COL( tbl.[name], idx.indid, 23 ) AS col23,
	INDEX_COL( tbl.[name], idx.indid, 24 ) AS col24,
	INDEX_COL( tbl.[name], idx.indid, 25 ) AS col25,
	dpages,
	used,
	rowcnt
FROM
	sysindexes idx
INNER JOIN sys.objects tbl
	ON idx.[id] = tbl.object_id
WHERE
	tbl.is_ms_shipped = 0
	AND indid > 0 
	AND INDEXPROPERTY(tbl.object_id, idx.[name], 'IsStatistics') = 1
GO

CREATE VIEW vDuplicateStatistics AS
SELECT
	l1.TableName, 
	l1.IndexName, 
	l2.IndexName AS DuplicateIndex, 
	l1.col1, 
	l1.col2, 
	l1.col3, 
	l1.col4, 
	l1.col5, 
	l1.dpages,
	l1.used,
	l1.rowcnt
FROM
	vStatisticsWithColumns l1 
INNER JOIN vStatisticsWithColumns l2
	ON l1.TableName = l2.TableName
	AND l1.IndexName <> l2.IndexName
	AND l1.col1 = l2.col1
	AND COALESCE(l1.col2,'') = COALESCE(l2.col2,'')
	AND COALESCE(l1.col3,'') = COALESCE(l2.col3,'')
	AND COALESCE(l1.col4,'') = COALESCE(l2.col4,'')
	AND COALESCE(l1.col5,'') = COALESCE(l2.col5,'')
	AND COALESCE(l1.col6,'') = COALESCE(l2.col6,'')
	AND COALESCE(l1.col7,'') = COALESCE(l2.col7,'')
	AND COALESCE(l1.col8,'') = COALESCE(l2.col8,'')
	AND COALESCE(l1.col9,'') = COALESCE(l2.col9,'')
	AND COALESCE(l1.col10,'') = COALESCE(l2.col10,'')
	AND COALESCE(l1.col11,'') = COALESCE(l2.col11,'')
	AND COALESCE(l1.col12,'') = COALESCE(l2.col12,'')
	AND COALESCE(l1.col13,'') = COALESCE(l2.col13,'')
	AND COALESCE(l1.col14,'') = COALESCE(l2.col14,'')
	AND COALESCE(l1.col15,'') = COALESCE(l2.col15,'')
	AND COALESCE(l1.col16,'') = COALESCE(l2.col16,'')
	AND COALESCE(l1.col17,'') = COALESCE(l2.col17,'')
	AND COALESCE(l1.col18,'') = COALESCE(l2.col18,'')
	AND COALESCE(l1.col19,'') = COALESCE(l2.col19,'')
	AND COALESCE(l1.col20,'') = COALESCE(l2.col20,'')
	AND COALESCE(l1.col21,'') = COALESCE(l2.col21,'')
	AND COALESCE(l1.col22,'') = COALESCE(l2.col22,'')
	AND COALESCE(l1.col23,'') = COALESCE(l2.col23,'')
	AND COALESCE(l1.col24,'') = COALESCE(l2.col24,'')
	AND COALESCE(l1.col25,'') = COALESCE(l2.col25,'')
GO


Is this code snippet, product or advice warrantied against ill-effect and/or technical malaise? No. No it's not! Not expressed - Not implied - not at all.



Tags:
 Maintenance    Snippet    SQL Server    Statistics    View  

Created by Josh Patterson on 2/9/2013, last modified by Josh Patterson on 2/17/2013

No comments currently exists for this page. Why don't you add one?
First Previous Next Last 

Login or signup to leave a comment.
 
Copyright © 2014 NetworkDLS.
All rights reserved.
 
Privacy Policy | Our Company | Contact