Vous êtes ici :
Cette requête produit une liste détaillée des statistiques de la base de données courante.
Transact-SQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 |
/*------------------------------------------------------------------- [SCRIPT] statistics list [DATABASE] current [DESCRIPTION] informations about all statistics in the current database [MAJ PAR] DATAFLY - Arian Papillon -------------------------------------------------------------------*/ SELECT s.object_id , SCHEMA_NAME(o.schema_id) AS schema_name , OBJECT_NAME(s.object_id) AS object_name , s.name AS stats_name , s.stats_id -- , sc.stats_column_id , s.has_filter , s.filter_definition , s.auto_created , s.no_recompute , s.is_temporary , s.is_incremental , STUFF( ( SELECT ', ' + c.name AS [text()] FROM sys.stats_columns AS sc LEFT JOIN(sys.indexes i JOIN sys.index_columns AS ic ON ic.object_id = i.object_id AND i.index_id = ic.index_id)ON i.object_id = sc.object_id AND i.name = s.name AND ic.column_id = sc.column_id JOIN sys.columns AS c ON c.object_id = sc.object_id AND c.column_id = sc.column_id WHERE s.stats_id = sc.stats_id AND s.object_id = sc.object_id ORDER BY CASE WHEN s.auto_created = 1 OR s.user_created = 1 THEN sc.stats_column_id ELSE ic.key_ordinal END FOR XML PATH('')) , 1 , 1 , '') AS stats_column_list /* -- bug dans sys.stats, connect 1163126 https://connect.microsoft.com/SQLServer/feedback/details/1163126/unexpected-stats-column-id-behavior ,SUBSTRING((SELECT ','+c.name AS [text()] FROM sys.stats_columns AS sc LEFT JOIN sys.columns AS c ON c.object_id = s.object_id AND c.object_id = sc.object_id AND c.column_id = sc.column_id WHERE s.stats_id = sc.stats_id AND s.object_id = sc.object_id ORDER BY sc.stats_column_id FOR XML PATH('')),2,1000) StatCols */ , sp.last_updated , tc.table_rows , sp.rows AS stat_rows , sp.rows_sampled , CAST(( CAST(sp.rows_sampled AS DECIMAL(18, 3)) / ( sp.rows )) * 100 AS DECIMAL(18, 2)) AS sample , CASE WHEN sp.rows = sp.rows_sampled THEN 'Yes' ELSE 'No' END AS FullScan , sp.steps , sp.unfiltered_rows , sp.modification_counter , CAST((( CAST(sp.modification_counter AS DECIMAL(18, 5)) / tc.table_rows ) * 100 ) AS DECIMAL(18, 2)) AS ratio_modification FROM sys.stats AS s JOIN sys.objects o ON s.object_id = o.object_id JOIN ( SELECT t.object_id , SUM(p.rows) AS table_rows FROM sys.objects t JOIN sys.indexes i ON t.object_id = i.object_id JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id WHERE t.type = 'U' AND i.index_id < 2 GROUP BY t.object_id ) AS tc ON o.object_id = tc.object_id CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1 AND OBJECTPROPERTY(s.object_id, 'IsMSShipped') = 0 AND OBJECT_NAME(s.object_id) <> 'sysdiagrams' -- seulement tables utilisateur --ORDER BY CAST ((CAST(sp.rows_sampled AS DECIMAL(18,3)) / (sp.rows)) * 100 AS DECIMAL(18,2)) ORDER BY OBJECT_NAME(s.object_id) , s.stats_id; |
Table of Contents