by
1
6
328k
0
Top 1% !
Popular
Pearl of Wisdom
Easy-to-find
Specified
OpenSource
Popularity: 3095th place
DBMSMS SQL Server
LanguageSQL
LicenseMIT_X11

Retrieve useful information regarding indexes in MS SQL Server

This script helps the DBA to retrieve valuable information regarding indexes.
Here the complete explanation of the output fields:
object_type: type of object (will either be "U" for User-Defined Table or "V" for View)
database_name: name of the database
schema_name: name of the schema
object_name: name of the object
create_date: date the object was created (time portion truncated, meant to give a general idea of how long ago the object was created)
modify_date: date the object was last modified (time portion truncated, will contain ".................." if it is the same date as the create_date column)
rows: total rows of data contained within the object
total_mb: total size of the object in megabytes
used_mb: total space used in megabytes
unused_mb: space reserved but not yet allocated in megabytes
data_mb: space used by data in megabytes
index_mb: space used by indexes in megabytes
pct_data: percentage of space used which is allocated to data
pct_index: percentage of space used which is allocated to indexes
index_type: type of index (Clustered, Nonclustered, Heap)
index_name: name of the index
system_named: indicates if the index name was generated by SQL Server or explicitly named (applicable to PKs and Unique Constraints / Indexes only)
is_pk: indicates if the index is a primary key
unique: indicates if the index is unique and whether its uniqueness if enforced by the index definition (displayed as "I") or a constraint (displayed as "C")
disabled: indicates if the index is disabled
hypothetical: indicates if the index is hypothetical
total_columns: number of columns which comprise the entire object
index_columns: number of columns which comprise the index key
include_columns: number of columns which comprise the include key
index_pct_of_columns: percentage of columns that make up the index as compared to the entire object
include_pct_of_columns: percentage of columns that make up the include as compared to the entire object
total_pct_of_columns: percentage of columns that make up the index + include as compared to the entire object
key_mb: space used by this particular index in megabytes
key_mb_pct: percentage of space used as compared to total space allocated to all indexes on the object
max_key_size_bytes: maximum possible byte size of the index based on the data size(s) of the column(s) involved
over_key_size_limit: indicates if the index key size exceeds the 900 byte limit
index_key: list of columns which comprise the index (ordinal sort) showing column name, data type, size, precision, scale, and sort order (ascending / descending)
include_key: list of columns which comprise the include portion of the index (ordinal sort) showing column name, data type, size, precision, scale, and sort order (ascending / descending)
filter_definition: filtered index definition / criteria
dupe_id: Indicates if the index is a duplicate (assigns a random "dupe group" ID and shows a count of total times the index is duplicated)
unused: indicates if the index is unused and therefore can potentially be dropped (does not look at PKs, Indexed FKs, Clustered Indexes, Unique Indexes / Constraints)
statistics_date: date when statistics were last updated on the index (blank for Heaps)
row_locks: indicates if the index allows row locks
page_locks: indicates if the index allows page locks
ignore_dupes: determines the error response when an insert operation attempts to insert duplicate key values into a unique index
auto_stats: indicates if statistics are automatically recomputed
padded: indicates if index padding is used
fill_factor: percentage of space to use when the index is created / rebuilt
user_seeks: total seeks by user queries
user_scans: total scans by user queries
user_lookups: total bookmark lookups by user queries
user_updates: total updates by user queries
last_user_seek: date of last user seek (time portion truncated)
last_user_scan: date of last user scan (time portion truncated)
last_user_lookup: date of last user lookup (time portion truncated)
last_user_update: date of last user update (time portion truncated)
system_seeks: total seeks by system queries
system_scans: total scans by system queries
system_lookups: total bookmark lookups by system queries
system_updates: total updates by system queries
last_system_seek: date of last system seek (time portion truncated)
last_system_scan: date of last system scan (time portion truncated)
last_system_lookup: date of last system lookup (time portion truncated)
last_system_update: date of last system update (time portion truncated)

Many thanks to Sean Smith. Check the original article here: http://www.sqlservercentral.com/scripts/index/104395/
Copy Embed Code
<iframe id="embedFrame" style="width:600px; height:300px;"
src="https://www.snip2code.com/Embed/46114/Retrieve-useful-information-regarding-in?startLine=0"></iframe>
Click on the embed code to copy it into your clipboard Width Height
Leave empty to retrieve all the content Start End
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SET NOCOUNT ON SET ANSI_WARNINGS OFF SET ARITHABORT OFF SET ARITHIGNORE ON SET TEXTSIZE 2147483647 ----------------------------------------------------------------------------------------------------------------------------- -- Declarations / Sets: Declare And Set Variables ----------------------------------------------------------------------------------------------------------------------------- DECLARE @Database_ID AS SMALLINT SET @Database_ID = DB_ID () ----------------------------------------------------------------------------------------------------------------------------- -- Error Trapping: Check If Temp Table(s) Already Exist(s) And Drop If Applicable ----------------------------------------------------------------------------------------------------------------------------- IF OBJECT_ID (N'tempdb.dbo.#temp_index_breakdown_keys_filters', N'U') IS NOT NULL BEGIN DROP TABLE dbo.#temp_index_breakdown_keys_filters END IF OBJECT_ID (N'tempdb.dbo.#temp_index_breakdown_size_info', N'U') IS NOT NULL BEGIN DROP TABLE dbo.#temp_index_breakdown_size_info END ----------------------------------------------------------------------------------------------------------------------------- -- Table Insert: Insert Index Key, Include Key, And Filter Definition Values Into Temp Table ----------------------------------------------------------------------------------------------------------------------------- SELECT sqI.[object_id] ,sqI.index_id ,STUFF (CONVERT (NVARCHAR (MAX), sqI.index_key), 1, 2, N'') AS index_key ,STUFF (CONVERT (NVARCHAR (MAX), sqI.include_key), 1, 2, N'') AS include_key ,sqI.filter_definition ,RANK () OVER ( ORDER BY sqI.[object_id] ,CONVERT (NVARCHAR (MAX), sqI.index_key) ,sqI.filter_definition ) AS dupe_rank INTO dbo.#temp_index_breakdown_keys_filters FROM ( SELECT I.[object_id] ,I.index_id ,( SELECT N', ' + C.name + N' • ' + TYPE_NAME (C.user_type_id) + ISNULL ((N': [ ' + (CASE WHEN C.system_type_id <> C.user_type_id THEN LOWER (TYPE_NAME (C.system_type_id)) END) + N' ]'), N'') + N' ' + (CASE WHEN TY.name NOT IN (N'bigint', N'bit', N'date', N'datetime', N'datetime2', N'datetimeoffset', N'decimal', N'float', N'int', N'money', N'numeric', N'real', N'smalldatetime', N'smallint', N'smallmoney', N'time', N'tinyint') THEN CONVERT (NVARCHAR (30), C.max_length) ELSE CONVERT (NVARCHAR (30), C.max_length) + N' (' + CONVERT (NVARCHAR (30), COLUMNPROPERTY (C.[object_id], C.name, 'Precision')) + N',' + ISNULL (CONVERT (NVARCHAR (30), COLUMNPROPERTY (C.[object_id], C.name, 'Scale')), 0) + N')' END) + N' ' + (CASE WHEN IC.is_descending_key = 0 THEN N'[A]' WHEN IC.is_descending_key = 1 THEN N'[D]' ELSE N'[N/A]' END) AS [text()] FROM sys.index_columns IC INNER JOIN sys.columns C ON C.[object_id] = IC.[object_id] AND C.column_id = IC.column_id INNER JOIN sys.types TY ON TY.user_type_id = C.user_type_id WHERE IC.is_included_column = 0 AND IC.[object_id] = I.[object_id] AND IC.index_id = I.index_id ORDER BY IC.key_ordinal FOR XML PATH ('') ,TYPE ) AS index_key ,( SELECT N', ' + C.name + N' • ' + TYPE_NAME (C.user_type_id) + ISNULL ((N': [ ' + (CASE WHEN C.system_type_id <> C.user_type_id THEN LOWER (TYPE_NAME (C.system_type_id)) END) + N' ]'), N'') + N' ' + (CASE WHEN TY.name NOT IN (N'bigint', N'bit', N'date', N'datetime', N'datetime2', N'datetimeoffset', N'decimal', N'float', N'int', N'money', N'numeric', N'real', N'smalldatetime', N'smallint', N'smallmoney', N'time', N'tinyint') THEN CONVERT (NVARCHAR (30), C.max_length) ELSE CONVERT (NVARCHAR (30), C.max_length) + N' (' + CONVERT (NVARCHAR (30), COLUMNPROPERTY (C.[object_id], C.name, 'Precision')) + N',' + ISNULL (CONVERT (NVARCHAR (30), COLUMNPROPERTY (C.[object_id], C.name, 'Scale')), 0) + N')' END) AS [text()] FROM sys.index_columns IC INNER JOIN sys.columns C ON C.[object_id] = IC.[object_id] AND C.column_id = IC.column_id INNER JOIN sys.types TY ON TY.user_type_id = C.user_type_id WHERE IC.is_included_column = 1 AND IC.[object_id] = I.[object_id] AND IC.index_id = I.index_id ORDER BY IC.key_ordinal FOR XML PATH ('') ,TYPE ) AS include_key ,I.filter_definition FROM sys.indexes I ) sqI ----------------------------------------------------------------------------------------------------------------------------- -- Table Insert: Insert Size Values Into Temp Table ----------------------------------------------------------------------------------------------------------------------------- SELECT DDPS.[object_id] ,DDPS.index_id ,SUM (CASE WHEN DDPS.index_id < 2 THEN DDPS.row_count END) AS [rows] ,SUM (DDPS.reserved_page_count) AS total_pages ,SUM (DDPS.used_page_count) AS used_pages ,SUM (CASE WHEN DDPS.index_id < 2 THEN DDPS.in_row_data_page_count + DDPS.lob_used_page_count + DDPS.row_overflow_used_page_count ELSE DDPS.lob_used_page_count + DDPS.row_overflow_used_page_count END) AS data_pages INTO dbo.#temp_index_breakdown_size_info FROM sys.dm_db_partition_stats DDPS GROUP BY DDPS.[object_id] ,DDPS.index_id ----------------------------------------------------------------------------------------------------------------------------- -- Main Query: Final Display / Output ----------------------------------------------------------------------------------------------------------------------------- SELECT (CASE WHEN sqBAQ.row_filter = 1 THEN sqBAQ.[type] ELSE '' END) AS object_type ,(CASE WHEN sqBAQ.row_filter = 1 THEN DB_NAME () ELSE '' END) AS [database_name] ,(CASE WHEN sqBAQ.row_filter = 1 THEN SCHEMA_NAME (sqBAQ.[schema_id]) ELSE '' END) AS [schema_name] ,(CASE WHEN sqBAQ.row_filter = 1 THEN OBJECT_NAME (sqBAQ.[object_id]) ELSE '' END) AS [object_name] ,(CASE WHEN sqBAQ.row_filter = 1 THEN sqBAQ.create_date ELSE '' END) AS create_date ,(CASE WHEN sqBAQ.row_filter = 1 THEN caMDKL.modify_date ELSE '' END) AS modify_date ,(CASE WHEN sqBAQ.row_filter = 1 THEN REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (25), CONVERT (MONEY, sqBAQ.[rows]), 1)), 4, 22)) ELSE '' END) AS [rows] ,(CASE WHEN sqBAQ.row_filter = 1 THEN CONVERT (VARCHAR (25), CONVERT (MONEY, (sqBAQ.total_pages * 8) / 1024.0), 1) ELSE '' END) AS total_mb ,(CASE WHEN sqBAQ.row_filter = 1 THEN CONVERT (VARCHAR (25), CONVERT (MONEY, (sqBAQ.used_pages * 8) / 1024.0), 1) ELSE '' END) AS used_mb ,(CASE WHEN sqBAQ.row_filter = 1 THEN CONVERT (VARCHAR (25), CONVERT (MONEY, (sqBAQ.unused_pages * 8) / 1024.0), 1) ELSE '' END) AS unused_mb ,(CASE WHEN sqBAQ.row_filter = 1 THEN CONVERT (VARCHAR (25), CONVERT (MONEY, (sqBAQ.data_pages * 8) / 1024.0), 1) ELSE '' END) AS data_mb ,(CASE WHEN sqBAQ.row_filter = 1 THEN CONVERT (VARCHAR (25), CONVERT (MONEY, (sqBAQ.index_pages * 8) / 1024.0), 1) ELSE '' END) AS index_mb ,(CASE WHEN sqBAQ.row_filter = 1 THEN CONVERT (VARCHAR (6), CONVERT (DECIMAL (5, 2), ISNULL (((sqBAQ.data_pages + .0) / sqBAQ.used_pages) * 100, 0))) ELSE '' END) AS pct_data ,(CASE WHEN sqBAQ.row_filter = 1 THEN CONVERT (VARCHAR (6), CONVERT (DECIMAL (5, 2), ISNULL (((sqBAQ.index_pages + .0) / sqBAQ.used_pages) * 100, 0))) ELSE '' END) AS pct_index ,sqBAQ.type_desc AS index_type ,ISNULL (sqBAQ.index_name, '') AS index_name ,(CASE WHEN sqBAQ.type_desc = N'HEAP' THEN '' WHEN sqBAQ.is_primary_key = 0 AND sqBAQ.is_unique = 0 THEN REPLICATE ('.', 6) WHEN sqBAQ.is_system_named = 0 THEN 'No' WHEN sqBAQ.is_system_named = 1 THEN 'Yes' ELSE '' END) AS system_named ,(CASE WHEN sqBAQ.is_primary_key = 1 THEN 'Yes' ELSE '' END) AS is_pk ,(CASE WHEN sqBAQ.is_unique_constraint = 1 THEN 'C' WHEN sqBAQ.is_unique = 1 THEN 'I' ELSE '' END) AS [unique] ,(CASE WHEN sqBAQ.is_disabled = 1 THEN 'Yes' ELSE '' END) AS [disabled] ,(CASE WHEN sqBAQ.is_hypothetical = 1 THEN 'Yes' ELSE '' END) AS hypothetical ,REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (23), CONVERT (MONEY, sqCC.total_columns), 1)), 4, 23)) AS total_columns ,(CASE WHEN sqBAQ.type_desc = N'HEAP' THEN '' ELSE REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (23), CONVERT (MONEY, caMDKL.[index_columns]), 1)), 4, 23)) END) AS [index_columns] ,(CASE WHEN sqBAQ.type_desc = N'HEAP' THEN '' ELSE REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (23), CONVERT (MONEY, caMDKL.include_columns), 1)), 4, 23)) END) AS include_columns ,(CASE WHEN sqBAQ.type_desc = N'HEAP' THEN '' ELSE REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (23), CONVERT (MONEY, (caMDKL.[index_columns] / sqCC.total_columns) * 100), 1)), 1, 23)) END) AS index_pct_of_columns ,(CASE WHEN sqBAQ.type_desc = N'HEAP' THEN '' ELSE REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (23), CONVERT (MONEY, (caMDKL.include_columns / sqCC.total_columns) * 100), 1)), 1, 23)) END) AS include_pct_of_columns ,(CASE WHEN sqBAQ.type_desc = N'HEAP' THEN '' ELSE REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (23), CONVERT (MONEY, ((caMDKL.[index_columns] + caMDKL.include_columns) / sqCC.total_columns) * 100), 1)), 1, 23)) END) AS total_pct_of_columns ,CONVERT (VARCHAR (25), CONVERT (MONEY, (ISNULL (sqBAQ.individual_index_pages, 0) * 8) / 1024.0), 1) AS key_mb ,CONVERT (VARCHAR (6), CONVERT (DECIMAL (5, 2), ISNULL (((sqBAQ.individual_index_pages + .0) / sqBAQ.index_pages) * 100, 0))) AS key_mb_pct ,(CASE WHEN sqBAQ.type_desc = N'HEAP' THEN '' ELSE REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (25), CONVERT (MONEY, sqKL.total_max_length), 1)), 4, 22)) END) AS max_key_size_bytes ,(CASE WHEN sqKL.total_max_length > 900 THEN 'Yes' ELSE '' END) AS over_key_size_limit ,ISNULL (ttIBKF.index_key, N'') AS index_key ,ISNULL (ttIBKF.include_key, N'') AS include_key ,ISNULL (ttIBKF.filter_definition, N'') AS filter_definition ,(CASE WHEN sqED02.dupe_id IS NOT NULL THEN CONVERT (VARCHAR (20), sqED02.dupe_id) + ' - [' + CONVERT (VARCHAR (11), sqED02.total_dupes) + ']' ELSE '' END) AS dupe_id ,sqBAQ.is_unused AS unused ,ISNULL (CONVERT (VARCHAR (10), STATS_DATE (sqBAQ.[object_id], sqBAQ.index_id), 23), '') AS statistics_date ,(CASE WHEN sqBAQ.[allow_row_locks] = 0 THEN 'No' WHEN sqBAQ.[allow_row_locks] = 1 THEN 'Yes' ELSE '' END) AS row_locks ,(CASE WHEN sqBAQ.[allow_page_locks] = 0 THEN 'No' WHEN sqBAQ.[allow_page_locks] = 1 THEN 'Yes' ELSE '' END) AS page_locks ,(CASE WHEN sqBAQ.[ignore_dup_key] = 0 THEN 'No' WHEN sqBAQ.[ignore_dup_key] = 1 THEN 'Yes' ELSE '' END) AS ignore_dupes ,(CASE WHEN sqBAQ.no_recompute = 0 THEN 'Yes' WHEN sqBAQ.no_recompute = 1 THEN 'No' ELSE '' END) AS auto_stats ,(CASE WHEN sqBAQ.is_padded = 0 THEN 'No' WHEN sqBAQ.is_padded = 1 THEN 'Yes' ELSE '' END) AS padded ,(CASE WHEN sqBAQ.fill_factor = 0 THEN 100 ELSE sqBAQ.fill_factor END) AS fill_factor ,(CASE WHEN sqBAQ.user_seeks > 0 THEN CONVERT (VARCHAR (20), sqBAQ.user_seeks) ELSE '' END) AS user_seeks ,(CASE WHEN sqBAQ.user_scans > 0 THEN CONVERT (VARCHAR (20), sqBAQ.user_scans) ELSE '' END) AS user_scans ,(CASE WHEN sqBAQ.user_lookups > 0 THEN CONVERT (VARCHAR (20), sqBAQ.user_lookups) ELSE '' END) AS user_lookups ,(CASE WHEN sqBAQ.user_updates > 0 THEN CONVERT (VARCHAR (20), sqBAQ.user_updates) ELSE '' END) AS user_updates ,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_user_seek, 23), '') AS last_user_seek ,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_user_scan, 23), '') AS last_user_scan ,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_user_lookup, 23), '') AS last_user_lookup ,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_user_update, 23), '') AS last_user_update ,(CASE WHEN sqBAQ.system_seeks > 0 THEN CONVERT (VARCHAR (20), sqBAQ.system_seeks) ELSE '' END) AS system_seeks ,(CASE WHEN sqBAQ.system_scans > 0 THEN CONVERT (VARCHAR (20), sqBAQ.system_scans) ELSE '' END) AS system_scans ,(CASE WHEN sqBAQ.system_lookups > 0 THEN CONVERT (VARCHAR (20), sqBAQ.system_lookups) ELSE '' END) AS system_lookups ,(CASE WHEN sqBAQ.system_updates > 0 THEN CONVERT (VARCHAR (20), sqBAQ.system_updates) ELSE '' END) AS system_updates ,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_system_seek, 23), '') AS last_system_seek ,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_system_scan, 23), '') AS last_system_scan ,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_system_lookup, 23), '') AS last_system_lookup ,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_system_update, 23), '') AS last_system_update FROM ( SELECT O.[type] ,O.[schema_id] ,O.[object_id] ,CONVERT (VARCHAR (10), O.create_date, 23) AS create_date ,CONVERT (VARCHAR (10), O.modify_date, 23) AS modify_date ,sqDDPS.[rows] ,sqDDPS.total_pages ,sqDDPS.used_pages ,(CASE WHEN sqDDPS.total_pages > sqDDPS.used_pages THEN sqDDPS.total_pages - sqDDPS.used_pages ELSE 0 END) AS unused_pages ,sqDDPS.data_pages ,(CASE WHEN sqDDPS.used_pages > sqDDPS.data_pages THEN sqDDPS.used_pages - sqDDPS.data_pages ELSE 0 END) AS index_pages ,sqI.type_desc ,sqI.name AS index_name ,sqI.is_system_named ,sqI.is_primary_key ,sqI.is_unique ,sqI.is_disabled ,sqI.is_hypothetical ,sqI.individual_index_pages ,sqI.is_unused ,sqI.[allow_row_locks] ,sqI.[allow_page_locks] ,sqI.[ignore_dup_key] ,sqI.no_recompute ,sqI.is_padded ,sqI.fill_factor ,sqI.user_seeks ,sqI.user_scans ,sqI.user_lookups ,sqI.user_updates ,sqI.last_user_seek ,sqI.last_user_scan ,sqI.last_user_lookup ,sqI.last_user_update ,sqI.system_seeks ,sqI.system_scans ,sqI.system_lookups ,sqI.system_updates ,sqI.last_system_seek ,sqI.last_system_scan ,sqI.last_system_lookup ,sqI.last_system_update ,sqI.is_unique_constraint ,sqI.index_id ,sqI.row_filter FROM sys.objects O INNER JOIN ( SELECT ttIBSI.[object_id] ,SUM (ttIBSI.[rows]) AS [rows] ,SUM (ttIBSI.total_pages) AS total_pages ,SUM (ttIBSI.used_pages) AS used_pages ,SUM (ttIBSI.data_pages) AS data_pages FROM dbo.#temp_index_breakdown_size_info ttIBSI GROUP BY ttIBSI.[object_id] ) sqDDPS ON sqDDPS.[object_id] = O.[object_id] INNER JOIN ( SELECT I.[object_id] ,I.type_desc ,I.name ,KC.is_system_named ,I.is_primary_key ,I.is_unique ,I.is_disabled ,I.is_hypothetical ,(CASE WHEN ttIBSI.used_pages > ttIBSI.data_pages THEN ttIBSI.used_pages - ttIBSI.data_pages END) AS individual_index_pages ,(CASE WHEN I.[type] = 0 THEN '' WHEN I.[type] = 1 THEN REPLICATE ('.', 6) WHEN I.is_primary_key = 1 THEN REPLICATE ('.', 6) WHEN I.is_unique = 1 THEN REPLICATE ('.', 6) WHEN EXISTS ( SELECT * FROM sys.index_columns IC INNER JOIN sys.foreign_key_columns FKC ON FKC.parent_object_id = IC.[object_id] AND FKC.parent_column_id = IC.column_id WHERE IC.[object_id] = I.[object_id] AND IC.index_id = I.index_id ) THEN REPLICATE ('.', 6) WHEN DDIUS.[object_id] IS NOT NULL THEN (CASE WHEN DDIUS.user_seeks + DDIUS.user_scans + DDIUS.user_lookups + DDIUS.user_updates = 0 THEN 'Y/N' ELSE 'No' END) ELSE 'Yes' END) AS is_unused ,I.[allow_row_locks] ,I.[allow_page_locks] ,I.[ignore_dup_key] ,S.no_recompute ,I.is_padded ,I.fill_factor ,DDIUS.user_seeks ,DDIUS.user_scans ,DDIUS.user_lookups ,DDIUS.user_updates ,DDIUS.last_user_seek ,DDIUS.last_user_scan ,DDIUS.last_user_lookup ,DDIUS.last_user_update ,DDIUS.system_seeks ,DDIUS.system_scans ,DDIUS.system_lookups ,DDIUS.system_updates ,DDIUS.last_system_seek ,DDIUS.last_system_scan ,DDIUS.last_system_lookup ,DDIUS.last_system_update ,I.is_unique_constraint ,I.index_id ,ROW_NUMBER () OVER ( PARTITION BY I.[object_id] ORDER BY I.is_primary_key DESC ,(CASE WHEN I.[type] = 0 THEN 'Z' ELSE 'A' END) ,I.[type] ,I.name ) AS row_filter FROM sys.indexes I INNER JOIN dbo.#temp_index_breakdown_size_info ttIBSI ON ttIBSI.[object_id] = I.[object_id] AND ttIBSI.index_id = I.index_id LEFT JOIN sys.key_constraints KC ON KC.parent_object_id = I.[object_id] AND KC.unique_index_id = I.index_id LEFT JOIN sys.stats S ON S.[object_id] = I.[object_id] AND S.stats_id = I.index_id LEFT JOIN master.sys.dm_db_index_usage_stats DDIUS ON DDIUS.[object_id] = I.[object_id] AND DDIUS.index_id = I.index_id AND DDIUS.database_id = @Database_ID ) sqI ON sqI.[object_id] = O.[object_id] WHERE O.[type] IN ('U', 'V') AND O.is_ms_shipped = 0 AND NOT ( SCHEMA_NAME (O.[schema_id]) = N'dbo' AND O.name = N'sysdiagrams' AND O.[type] = 'U' ) ) sqBAQ INNER JOIN ( SELECT C.[object_id] ,COUNT (*) + .0 AS total_columns FROM sys.columns C GROUP BY C.[object_id] ) sqCC ON sqCC.[object_id] = sqBAQ.[object_id] LEFT JOIN dbo.#temp_index_breakdown_keys_filters ttIBKF ON ttIBKF.[object_id] = sqBAQ.[object_id] AND ttIBKF.index_id = sqBAQ.index_id LEFT JOIN ( SELECT IC.[object_id] ,IC.index_id ,SUM (C.max_length) AS total_max_length FROM sys.index_columns IC INNER JOIN sys.columns C ON C.[object_id] = IC.[object_id] AND C.column_id = IC.column_id WHERE IC.is_included_column = 0 GROUP BY IC.[object_id] ,IC.index_id ) sqKL ON sqKL.[object_id] = sqBAQ.[object_id] AND sqKL.index_id = sqBAQ.index_id LEFT JOIN ( SELECT sqED01.dupe_rank ,sqED01.total_dupes ,ROW_NUMBER () OVER ( ORDER BY (SELECT NULL) ) AS dupe_id FROM ( SELECT ttIBKF.dupe_rank ,COUNT (*) AS total_dupes FROM dbo.#temp_index_breakdown_keys_filters ttIBKF GROUP BY ttIBKF.dupe_rank HAVING COUNT (*) > 1 ) sqED01 ) sqED02 ON sqED02.dupe_rank = ttIBKF.dupe_rank CROSS APPLY ( SELECT (CASE WHEN sqBAQ.modify_date = sqBAQ.create_date THEN REPLICATE ('.', 18) ELSE sqBAQ.modify_date END) AS modify_date ,LEN (ttIBKF.index_key) - LEN (REPLACE (ttIBKF.index_key, '•', '')) AS [index_columns] ,ISNULL (LEN (ttIBKF.include_key) - LEN (REPLACE (ttIBKF.include_key, '•', '')), 0) AS include_columns ) caMDKL ORDER BY sqBAQ.[type] ,SCHEMA_NAME (sqBAQ.[schema_id]) ,OBJECT_NAME (sqBAQ.[object_id]) ,sqBAQ.row_filter ----------------------------------------------------------------------------------------------------------------------------- -- Cleanup: Drop Any Remaining Temp Tables ----------------------------------------------------------------------------------------------------------------------------- IF OBJECT_ID (N'tempdb.dbo.#temp_index_breakdown_keys_filters', N'U') IS NOT NULL BEGIN DROP TABLE dbo.#temp_index_breakdown_keys_filters END IF OBJECT_ID (N'tempdb.dbo.#temp_index_breakdown_size_info', N'U') IS NOT NULL BEGIN DROP TABLE dbo.#temp_index_breakdown_size_info END
If you want to be updated about similar snippets, Sign in and follow our Channels

blog comments powered by Disqus