Posted By

icebob on 08/17/11


Tagged

mssql indexinfo


Versions (?)

Mssql indexinfo


 / Published in: SQL
 

  1. USE master
  2. GO
  3. IF OBJECT_ID('sp_indexinfo') IS NOT NULL DROP PROC sp_indexinfo
  4. GO
  5.  
  6. CREATE PROCEDURE sp_IndexInfo
  7. @tblPat sysname = '%'
  8. ,@missing_ix tinyint = 1
  9. AS
  10. --Written by Tibor Karaszi 2008-07-07
  11. --Last modified by Tibor Karaszi 2008-07-10
  12. WITH key_columns AS
  13. (
  14. SELECT c.OBJECT_ID, c.name AS column_name, ic.key_ordinal, ic.is_included_column, ic.index_id, ic.is_descending_key
  15. FROM sys.COLUMNS AS c
  16. INNER JOIN sys.index_columns AS ic ON c.OBJECT_ID = ic.OBJECT_ID AND ic.column_id = c.column_id
  17. )
  18. , physical_info AS
  19. (
  20. SELECT p.OBJECT_ID, p.index_id, ds.name AS location, SUM(p.rows) AS rows, SUM(a.total_pages) AS pages
  21. FROM sys.partitions AS p
  22. INNER JOIN sys.allocation_units AS a ON p.hobt_id = a.container_id
  23. INNER JOIN sys.data_spaces AS ds ON a.data_space_id = ds.data_space_id
  24. GROUP BY OBJECT_ID, index_id, ds.name
  25. )
  26. SELECT
  27. OBJECT_SCHEMA_NAME(i.OBJECT_ID) AS schema_name
  28. ,OBJECT_NAME(i.OBJECT_ID) AS table_name
  29. ,i.name AS index_name
  30. ,CASE i.type WHEN 0 THEN 'heap' WHEN 1 THEN 'cl' WHEN 2 THEN 'nc' WHEN 3 THEN 'xml' ELSE CAST(i.type AS VARCHAR(2)) END AS type
  31. ,i.is_unique
  32. ,CASE
  33. WHEN is_primary_key = 0 AND is_unique_constraint = 0 THEN 'no'
  34. WHEN is_primary_key = 1 AND is_unique_constraint = 0 THEN 'PK'
  35. WHEN is_primary_key = 0 AND is_unique_constraint = 1 THEN 'UQ'
  36. END
  37. AS cnstr
  38. ,(SELECT
  39. CAST(kc.column_name + CASE kc.is_descending_key WHEN 0 THEN '' ELSE ' DESC' END AS VARCHAR(MAX)) + ', ' AS [text()]
  40. FROM key_columns AS kc
  41. WHERE i.OBJECT_ID = kc.OBJECT_ID AND i.index_id = kc.index_id AND kc.is_included_column = 0
  42. ORDER BY key_ordinal
  43. FOR XML PATH('')
  44. ) AS key_columns
  45. ,(SELECT CAST(column_name AS VARCHAR(MAX)) + ', ' AS [text()]
  46. FROM key_columns AS kc
  47. WHERE i.OBJECT_ID = kc.OBJECT_ID AND i.index_id = kc.index_id AND kc.is_included_column = 1
  48. ORDER BY key_ordinal
  49. FOR XML PATH('')
  50. ) AS included_columns
  51. ,p.location
  52. ,p.rows
  53. ,p.pages
  54. ,CAST((p.pages * 8.00) / 1024 AS decimal(9,2)) AS MB
  55. ,s.user_seeks
  56. ,s.user_scans
  57. ,s.user_lookups
  58. ,s.user_updates
  59. FROM sys.indexes AS i
  60. INNER JOIN physical_info AS p ON i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id
  61. LEFT JOIN sys.dm_db_index_usage_stats AS s ON s.OBJECT_ID = i.OBJECT_ID AND s.index_id = i.index_id AND s.database_id = DB_ID()
  62. WHERE OBJECTPROPERTY(i.OBJECT_ID, 'IsMsShipped') = 0
  63. AND OBJECT_NAME(i.OBJECT_ID) LIKE @tblPat
  64. ORDER BY table_name, index_name
  65.  
  66. IF @missing_ix = 1
  67. BEGIN
  68. SELECT
  69. OBJECT_SCHEMA_NAME(d.OBJECT_ID) AS schema_name
  70. ,OBJECT_NAME(d.OBJECT_ID) AS table_name
  71. ,'CREATE INDEX <IndexName> ON ' + OBJECT_SCHEMA_NAME(d.OBJECT_ID) + '.' + OBJECT_NAME(d.OBJECT_ID) + ' '
  72. + '(' + COALESCE(d.equality_columns + COALESCE(', ' + d.inequality_columns, ''), d.inequality_columns) + ')'
  73. + COALESCE(' INCLUDE(' + d.included_columns + ')', '')
  74. AS ddl
  75. ,s.user_seeks
  76. ,s.user_scans
  77. ,s.avg_user_impact
  78. FROM sys.dm_db_missing_index_details AS d
  79. INNER JOIN sys.dm_db_missing_index_groups AS g ON d.index_handle = g.index_handle
  80. INNER JOIN sys.dm_db_missing_index_group_stats AS s ON g.index_group_handle = s.group_handle
  81. WHERE OBJECT_NAME(d.OBJECT_ID) LIKE @tblPat
  82. AND d.database_id = DB_ID()
  83. ORDER BY avg_user_impact DESC
  84. END
  85. GO
  86.  
  87. EXEC sp_MS_Marksystemobject sp_IndexInfo

Report this snippet  

You need to login to post a comment.