Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
T-SQL sp_helpindex3 replaces sp_helpindex2
USE master
GO
IF OBJECT_ID('sp_helpindex2', 'P') IS NOT NULL
BEGIN
DROP PROCEDURE sp_helpindex2
END
IF OBJECT_ID('sp_helpindex3', 'P') IS NOT NULL
BEGIN
DROP PROCEDURE sp_helpindex3
END
GO
-- =============================================
-- Author: Jared Dobson && Greg Wright && http://realsqlguy.blogspot.com/2008/04/include-columns-and-sphelpindex.html
-- Create date: 09/09/2010
-- Description: Give me more index information about this table like includes, duplicates etc.
-- Modification: Now with Index usage stats! :-)
-- : Greg Wright, add a parameter to specify new fillfactor
-- : Greg Wright, add a parameter to use existing, or new fillfactor if it doesn't exist
-- =============================================
CREATE PROCEDURE [dbo].[sp_helpindex3]
@objname NVARCHAR(776) -- the table to check for indexes
, @new_fillfactor INT = NULL -- if a new fillfactor is specified, use it, otherwise use what's there already there
, @force_new_fillfactor INT = 0 -- 1 = makes it that either New or Existing is included in script result
AS -- PRELIM
--IF (@new_fillfactor IS NOT NULL AND @force_fillfactor = 0)
-- SET @force_fillfactor = 1
SET nocount ON
DECLARE
@objid INT, -- the object id of the table
@indid SMALLINT, -- the index id of an index
@groupid INT, -- the filegroup id of an index
@indname SYSNAME,
@groupname SYSNAME,
@status INT,
@keys NVARCHAR(2126), --Length (16*max_identifierLength)+(15*2)+(16*3)
@include_cols NVARCHAR(2126),
@dbname SYSNAME,
@ignore_dup_key BIT,
@is_unique BIT,
@filter_definition NVARCHAR(MAX),
@is_hypothetical BIT,
@is_primary_key BIT,
@is_unique_key BIT,
@auto_created BIT,
@no_recompute BIT,
@last_user_seek DATETIME,
@last_user_scan DATETIME,
@last_user_lookup DATETIME,
@last_user_update DATETIME,
@user_seeks BIGINT,
@user_scans BIGINT,
@user_lookups BIGINT,
@user_updates BIGINT,
@orig_fillfactor INT ;
-- Check to see that the object names are local to the current database.
SELECT
@dbname = PARSENAME(@objname, 3)
IF @dbname IS NULL
SELECT
@dbname = DB_NAME()
ELSE
IF @dbname <> DB_NAME()
BEGIN
RAISERROR(15250,-1,-1)
RETURN (1)
END
-- Check to see the the table exists and initialize @objid.
SELECT
@objid = OBJECT_ID(@objname)
IF @objid IS NULL
BEGIN
RAISERROR(15009,-1,-1,@objname,@dbname)
RETURN (1)
END
-- OPEN CURSOR OVER INDEXES (skip stats: bug shiloh_51196)
DECLARE ms_crs_ind CURSOR local static
FOR
SELECT
i.index_id,
i.data_space_id,
i.name,
i.ignore_dup_key,
i.is_unique,
i.filter_definition,
i.is_hypothetical,
i.is_primary_key,
i.is_unique_constraint,
s.auto_created,
s.no_recompute,
[DDIUS].[last_user_seek],
[DDIUS].[last_user_scan],
[DDIUS].[last_user_lookup],
[DDIUS].[last_user_update],
[DDIUS].[user_seeks],
[DDIUS].[user_scans],
[DDIUS].[user_lookups],
[DDIUS].[user_updates],
i.fill_factor
FROM
sys.indexes i
JOIN sys.stats s
ON i.OBJECT_ID = s.OBJECT_ID
AND i.index_id = s.stats_id
CROSS APPLY ( SELECT
MAX([DDI].[last_user_seek]) AS [last_user_seek],
MAX([DDI].[last_user_scan]) AS [last_user_scan],
MAX([DDI].[last_user_lookup]) AS [last_user_lookup],
MAX([DDI].[last_user_update]) AS [last_user_update],
SUM(ISNULL([DDI].[user_seeks], 0)) AS [user_seeks],
SUM(ISNULL([DDI].[user_scans], 0)) AS [user_scans],
SUM(ISNULL([DDI].[user_lookups], 0)) AS [user_lookups],
SUM(ISNULL([DDI].[user_updates], 0)) AS [user_updates]
FROM
[sys].[dm_db_index_usage_stats] AS DDI
WHERE
[i].[index_id] = [DDI].[index_id]
AND [DDI].[object_id] = [i].[object_id]
GROUP BY
[DDI].[object_id],
[DDI].[index_id]
) AS [DDIUS]
WHERE
i.OBJECT_ID = @objid
OPEN ms_crs_ind
FETCH ms_crs_ind INTO @indid, @groupid, @indname, @ignore_dup_key,
@is_unique, @filter_definition, @is_hypothetical, @is_primary_key, @is_unique_key,
@auto_created, @no_recompute, @last_user_seek, @last_user_scan,
@last_user_lookup, @last_user_update, @user_seeks, @user_scans,
@user_lookups, @user_updates, @orig_fillfactor ;
-- IF NO INDEX, QUIT
IF @@fetch_status < 0
BEGIN
DEALLOCATE ms_crs_ind
RAISERROR(15472,-1,-1,@objname) -- Object does not have any indexes.
RETURN (0)
END
-- create temp table
CREATE TABLE #spindtab
(
index_name SYSNAME COLLATE database_default
NOT NULL,
index_id INT,
ignore_dup_key BIT,
is_unique BIT,
filter_definition NVARCHAR(MAX),
is_hypothetical BIT,
is_primary_key BIT,
is_unique_key BIT,
auto_created BIT,
no_recompute BIT,
groupname SYSNAME COLLATE database_default
NULL,
index_keys NVARCHAR(2126) COLLATE database_default
NOT NULL, -- see @keys above for length descr
includes NVARCHAR(2126) COLLATE database_default
NOT NULL,
last_user_seek DATETIME NULL,
last_user_scan DATETIME NULL,
last_user_lookup DATETIME NULL,
last_user_update DATETIME NULL,
user_seeks BIGINT,
user_scans BIGINT,
user_lookups BIGINT,
user_updates BIGINT,
orig_fillfactor INT
)
-- Now check out each index, figure out its type and keys and
-- save the info in a temporary table that we'll print out at the end.
WHILE @@fetch_status >= 0
BEGIN
-- First we'll figure out what the keys are.
DECLARE
@i INT,
@thiskey NVARCHAR(131) -- 128+3
SELECT
@keys = INDEX_COL(@objname, @indid, 1),
@i = 2
IF ( INDEXKEY_PROPERTY(@objid, @indid, 1, 'isdescending') = 1 )
SELECT
@keys = @keys + '(-)'
SELECT
@thiskey = INDEX_COL(@objname, @indid, @i)
IF ( ( @thiskey IS NOT NULL )
AND ( INDEXKEY_PROPERTY(@objid, @indid, @i, 'isdescending') = 1 ) )
SELECT
@thiskey = @thiskey + '(-)'
WHILE ( @thiskey IS NOT NULL )
BEGIN
SELECT
@keys = @keys + ', ' + @thiskey,
@i = @i + 1
SELECT
@thiskey = INDEX_COL(@objname, @indid, @i)
IF ( ( @thiskey IS NOT NULL )
AND ( INDEXKEY_PROPERTY(@objid, @indid, @i,
'isdescending') = 1 ) )
SELECT
@thiskey = @thiskey + '(-)'
END
SELECT
@groupname = NULL
SELECT
@groupname = name
FROM
sys.data_spaces
WHERE
data_space_id = @groupid DECLARE IncludeColsCursor CURSOR
FOR
SELECT
obj.name
FROM
sys.index_columns AS col
INNER JOIN sys.syscolumns AS obj
ON col.OBJECT_ID = obj.id
AND col.column_id = obj.colid
WHERE
is_included_column = 1
AND col.OBJECT_ID = @objid
AND col.index_id = @indid
ORDER BY
col.index_column_id
OPEN IncludeColsCursor
FETCH IncludeColsCursor INTO @thiskey
SET @include_cols = ''
WHILE @@FETCH_STATUS = 0
BEGIN
SET @include_cols = @include_cols
+ CASE WHEN @include_cols = '' THEN ''
ELSE ', '
END + @thiskey
FETCH IncludeColsCursor INTO @thiskey
END
CLOSE IncludeColsCursor
DEALLOCATE IncludeColsCursor
-- INSERT ROW FOR INDEX
INSERT INTO [#spindtab]
( [index_name],
[index_id],
[ignore_dup_key],
[is_unique],
[filter_definition],
[is_hypothetical],
[is_primary_key],
[is_unique_key],
[auto_created],
[no_recompute],
[groupname],
[index_keys],
[includes],
[last_user_seek],
[last_user_scan],
[last_user_lookup],
[last_user_update],
[user_seeks],
[user_scans],
[user_lookups],
[user_updates],
[orig_fillfactor] )
VALUES
( @indname,
@indid,
@ignore_dup_key,
@is_unique,
@filter_definition,
@is_hypothetical,
@is_primary_key,
@is_unique_key,
@auto_created,
@no_recompute,
@groupname,
@keys,
@include_cols,
@last_user_seek,
@last_user_scan,
@last_user_lookup,
@last_user_update,
@user_seeks,
@user_scans,
@user_lookups,
@user_updates,
@orig_fillfactor )
-- Next index
FETCH ms_crs_ind INTO @indid, @groupid, @indname, @ignore_dup_key,
@is_unique, @filter_definition, @is_hypothetical, @is_primary_key, @is_unique_key,
@auto_created, @no_recompute, @last_user_seek, @last_user_scan,
@last_user_lookup, @last_user_update, @user_seeks, @user_scans,
@user_lookups, @user_updates, @orig_fillfactor ;
END
DEALLOCATE ms_crs_ind
-- DISPLAY THE RESULTS
SELECT
'index_name' = index_name,
'index_id' = index_id,
'orig_fillfactor' = orig_fillfactor,
'index_description' = CONVERT(VARCHAR(210), --bits 16 off, 1, 2, 16777216 on, located on group
CASE WHEN index_id = 1 THEN 'clustered'
ELSE 'nonclustered'
END + CASE WHEN ignore_dup_key <> 0 THEN ', ignore duplicate keys'
ELSE ''
END + CASE WHEN is_unique <> 0 THEN ', unique'
ELSE ''
END + CASE WHEN is_hypothetical <> 0 THEN ', hypothetical'
ELSE ''
END
+ CASE WHEN is_primary_key <> 0 THEN ', primary key'
ELSE ''
END + CASE WHEN is_unique_key <> 0 THEN ', unique key'
ELSE ''
END + CASE WHEN auto_created <> 0 THEN ', auto create'
ELSE ''
END
+ CASE WHEN no_recompute <> 0 THEN ', stats no recompute'
ELSE ''
END + ' located on ' + groupname),
'index_keys' = index_keys,
'include_cols' = includes,
[filter_definition],
[last_user_seek],
[last_user_scan],
[last_user_lookup],
[last_user_update],
[user_seeks],
[user_scans],
[user_lookups],
[user_updates]
, 'ALTER INDEX [' + index_name + '] ON [' + @objname + '] REBUILD' +
CASE WHEN ISNULL(@force_new_fillfactor, 0) = 0 AND @new_fillfactor IS NULL THEN ';' -- Don't include the FILLFACTOR for maintenance rebuilds
WHEN ISNULL(@force_new_fillfactor, 0) = 0 AND orig_fillfactor != 0 THEN -- Don't include the FILLFACTOR for maintenance rebuilds
' WITH (FILLFACTOR = ' + CONVERT(VARCHAR(15), orig_fillfactor) + ');'
WHEN ISNULL(@force_new_fillfactor, 0) = 1 THEN -- Don't include the FILLFACTOR for maintenance rebuilds
' WITH (FILLFACTOR = ' + CONVERT(VARCHAR(15), COALESCE(@new_fillfactor, orig_fillfactor)) + ');'
ELSE ';'
END AS Rebuild_Text
, 'ALTER INDEX [' + index_name + '] ON [' + @objname + '] REORGANIZE;' AS Reorganize_Text
, 'DROP INDEX [' + @objname + '].[' + index_name + '];' AS Drop_Text
FROM
#spindtab
ORDER BY
index_id DESC
RETURN (0)
-- sp_helpindex3
GO
EXEC sp_MS_marksystemobject sp_helpindex3
GO
@hellomahesh

This comment has been minimized.

Copy link

@hellomahesh hellomahesh commented May 30, 2018

Index of a table NOT showing up if there is no usage stats on it. Bug?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.