Retrieve Index Information For All Tables - V2
This is an update to sp_helpindex, it allows you to pass the table name and whether you want to retrieve clustered, nonclustered or primary key indexes for a table. Using the sp_msforeach table will allow you to do this for all tables in a database. Inserting the data into a table will allow you to keep the information for reference purposes, or to recreate a lost index.
***Previous version did not account for multiple fiegroups, this one does, also includes script for recreation of indexes***
CREATE proc sp_nchelpindex
@objname varchar(256), -- the table to check for indexes
@CLUST VARCHAR(20) = 'ALL', -- CLUSTERED for Clustered Indexes only, NONCLUSTERED for nonclustered indexes only
@pk VARCHAR(10) = 'ALL' -- PKONLY for only PK Indexes, NOPK for Indexes that are not PKs
/*
CREATE IN MASTER DATABASE, THIS CAN THEN BE USED IN ANY DATABASE FOR THE INDEX INFORMATION.
USAGE:
SP_NCHELPINDEX @OBJNAME = THE TABLE NAME
@CLUST = CLUSTERED FOR CLUSTERED INDEXES, NONCLUSTERED FOR NONCLUSTERED OR LEAVE BLANK FOR ALL INDEXES
@PK = PKONLY FOR ONLY PRIMARY KEYS, NOPK FOR ONLY NON-PRIMARY KEYS OR LEAVE BLANK FOR ALL
ADVANCED USAGE:
Create a table to hold the data
CREATE TABLE #INDEXINFO
(TABLENAME VARCHAR(256),
INDEXNAME VARCHAR(256),
INDEX_KEYS VARCHAR(256),
ISCLUSTERED VARCHAR(3),
ISPRIMARY_KEY VARCHAR(3),
IDXFILEGROUP VARCHAR(100),
INDEX_DESCRIPTION VARCHAR(200))
USE SP_MSFOREACHTABLE to run the script against all tables in a database.
SP_MSFOREACHTABLE "INSERT INTO #INDEXINFO EXEC SP_NCHELPINDEX '?'" -- This inserts for all indexes
SP_MSFOREACHTABLE "INSERT INTO #INDEXINFO EXEC SP_NCHELPINDEX '?', 'CLUSTERED'" --This inserts only clustered index information
SP_MSFOREACHTABLE "INSERT INTO #INDEXINFO EXEC SP_NCHELPINDEX '?','','PKONLY'" -- This inserts only primary key index information
Select data from the table, can ultimately be used to create index creation scripts for possible lost indexes.
SELECT * FROM #INDEXINFO
Update table to remove [dbo].
UPDATE #INDEXINFO SET TABLENAME = REPLACE(TABLENAME, '[DBO].[','')
UPDATE #INDEXINFO SET TABLENAME = REPLACE(TABLENAME, ']','')
Script for index creation
declare @sql varchar(1000), @tab varchar(200), @idxname varchar(200), @idxkey varchar(200), @idxgrp varchar(100), @idxtype varchar(20)
select @idxname = min(indexname) from #indexinfo where indexname is not null and isprimary_key = 'NO'
while @idxname is not null
begin
select @tab = tablename from #indexinfo where indexname = @idxname
select @idxgrp = idxfilegroup from #indexinfo where indexname = @idxname
select @idxkey = index_keys from #indexinfo where indexname = @idxname
select @idxtype = case when isclustered = 'YES' then 'CLUSTERED' ELSE 'NONCLUSTERED' END from #indexinfo where indexname = @idxname
select @sql = 'create '+@idxtype+' index '+@idxname+' on '+@tab+'('+@idxkey+') WITH DROP_EXISTING on ['+@idxgrp+']'
print @sql
select @idxname = min(indexname) from #indexinfo where indexname is not null and indexname > @idxname and isprimary_key = 'NO'
end
*/
AS
-- PRELIM
set nocount on
declare @objid int, -- the object id of the table
@indid smallint, -- the index id of an index
@groupid smallint, -- the filegroup id of an index
@indname sysname,
@groupname sysname,
@status int,
@keys nvarchar(2126), --Length (16*max_identifierLength)+(15*2)+(16*3)
@dbname sysname,
@thedb sysname,
@sql varchar(1000)
create table #groupids (groupname varchar(256), groupid int)
select @thedb = db_name()
set @sql = 'insert into #groupids select groupname, groupid from '+@thedb+'..sysfilegroups'
exec (@sql)
-- Check to see that the object names are local to the current database.
select @dbname = parsename(@objname,3)
if @dbname is not null and @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
select @dbname=db_name()
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 indid, groupid, name, status from sysindexes
where id = @objid and indid > 0 and indid < 255 and (status & 64)=0 order by indid
open ms_crs_ind
fetch ms_crs_ind into @indid, @groupid, @indname, @status
-- IF NO INDEX, QUIT
if @@fetch_status < 0
begin
deallocate ms_crs_ind
raiserror(15472,-1,-1) --'Object does not have any indexes.'
return (0)
end
-- create temp table
create table #spindtab
(
index_name sysname collate database_default NOT NULL,
stats int,
groupname sysname collate database_default NULL,
index_keys nvarchar(2126) collate database_default NOT NULL -- see @keys above for length descr
)
-- 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 = groupname from #groupids where groupid = @groupid
-- INSERT ROW FOR INDEX
insert into #spindtab values (@indname, @status, @groupname, @keys)
-- Next index
fetch ms_crs_ind into @indid, @groupid, @indname, @status
end
deallocate ms_crs_ind
-- SET UP SOME CONSTANT VALUES FOR OUTPUT QUERY
declare @empty varchar(1) select @empty = ''
declare @des1 varchar(35), -- 35 matches spt_values
@des2 varchar(35),
@des4 varchar(35),
@des32 varchar(35),
@des64 varchar(35),
@des2048 varchar(35),
@des4096 varchar(35),
@des8388608 varchar(35),
@des16777216 varchar(35)
select @des1 = name from master.dbo.spt_values where type = 'I' and number = 1
select @des2 = name from master.dbo.spt_values where type = 'I' and number = 2
select @des4 = name from master.dbo.spt_values where type = 'I' and number = 4
select @des32 = name from master.dbo.spt_values where type = 'I' and number = 32
select @des64 = name from master.dbo.spt_values where type = 'I' and number = 64
select @des2048 = name from master.dbo.spt_values where type = 'I' and number = 2048
select @des4096 = name from master.dbo.spt_values where type = 'I' and number = 4096
select @des8388608 = name from master.dbo.spt_values where type = 'I' and number = 8388608
select @des16777216 = name from master.dbo.spt_values where type = 'I' and number = 16777216
-- DISPLAY THE RESULTS
IF @PK = 'PKONLY'
BEGIN
IF @CLUST = 'CLUSTERED'
BEGIN
select
'Tablename' = @objname,
'Index_name' = index_name,
'Index_keys' = index_keys,
'ISClustered' = case when (stats & 16) <> 0 then 'YES' else 'NO' end,
'ISPrimary_Key'= case when (stats & 2048)<>0 then 'YES' else 'NO' end,
'IDXFileGroup' = groupname,
'index_description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group
case when (stats & 16)<>0 then 'clustered' else 'nonclustered' end
+ case when (stats & 1)<>0 then ', '+@des1 else @empty end
+ case when (stats & 2)<>0 then ', '+@des2 else @empty end
+ case when (stats & 4)<>0 then ', '+@des4 else @empty end
+ case when (stats & 64)<>0 then ', '+@des64 else case when (stats & 32)<>0 then ', '+@des32 else @empty end end
+ case when (stats & 2048)<>0 then ', '+@des2048 else @empty end
+ case when (stats & 4096)<>0 then ', '+@des4096 else @empty end
+ case when (stats & 8388608)<>0 then ', '+@des8388608 else @empty end
+ case when (stats & 16777216)<>0 then ', '+@des16777216 else @empty end
+ ' located on ' + groupname)
from #spindtab
WHERE (STATS & 16) <> 0 AND (STATS & 2048) <> 0
order by index_name
END
ELSE
IF @CLUST = 'NONCLUSTERED'
BEGIN
select
'Tablename' = @objname,
'Index_name' = index_name,
'Index_keys' = index_keys,
'ISClustered' = case when (stats & 16) <> 0 then 'YES' else 'NO' end,
'ISPrimary_Key'= case when (stats & 2048)<>0 then 'YES' else 'NO' end,
'IDXFileGroup' = groupname,
'index_description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group
case when (stats & 16)<>0 then 'clustered' else 'nonclustered' end
+ case when (stats & 1)<>0 then ', '+@des1 else @empty end
+ case when (stats & 2)<>0 then ', '+@des2 else @empty end
+ case when (stats & 4)<>0 then ', '+@des4 else @empty end
+ case when (stats & 64)<>0 then ', '+@des64 else case when (stats & 32)<>0 then ', '+@des32 else @empty end end
+ case when (stats & 2048)<>0 then ', '+@des2048 else @empty end
+ case when (stats & 4096)<>0 then ', '+@des4096 else @empty end
+ case when (stats & 8388608)<>0 then ', '+@des8388608 else @empty end
+ case when (stats & 16777216)<>0 then ', '+@des16777216 else @empty end
+ ' located on ' + groupname)
from #spindtab
WHERE (STATS & 16) = 0 AND (STATS & 2048) <> 0
order by index_name
END
ELSE
BEGIN
select
'Tablename' = @objname,
'Index_name' = index_name,
'Index_keys' = index_keys,
'ISClustered' = case when (stats & 16) <> 0 then 'YES' else 'NO' end,
'ISPrimary_Key'= case when (stats & 2048)<>0 then 'YES' else 'NO' end,
'IDXFileGroup' = groupname,
'index_description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group
case when (stats & 16)<>0 then 'clustered' else 'nonclustered' end
+ case when (stats & 1)<>0 then ', '+@des1 else @empty end
+ case when (stats & 2)<>0 then ', '+@des2 else @empty end
+ case when (stats & 4)<>0 then ', '+@des4 else @empty end
+ case when (stats & 64)<>0 then ', '+@des64 else case when (stats & 32)<>0 then ', '+@des32 else @empty end end
+ case when (stats & 2048)<>0 then ', '+@des2048 else @empty end
+ case when (stats & 4096)<>0 then ', '+@des4096 else @empty end
+ case when (stats & 8388608)<>0 then ', '+@des8388608 else @empty end
+ case when (stats & 16777216)<>0 then ', '+@des16777216 else @empty end
+ ' located on ' + groupname)
from #spindtab WHERE (STATS & 2048) <> 0
order by index_name
END
END
ELSE
IF @PK = 'NOPK'
BEGIN
IF @CLUST = 'CLUSTERED'
BEGIN
select
'Tablename' = @objname,
'Index_name' = index_name,
'Index_keys' = index_keys,
'ISClustered' = case when (stats & 16) <> 0 then 'YES' else 'NO' end,
'ISPrimary_Key'= case when (stats & 2048)<>0 then 'YES' else 'NO' end,
'IDXFileGroup' = groupname,
'index_description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group
case when (stats & 16)<>0 then 'clustered' else 'nonclustered' end
+ case when (stats & 1)<>0 then ', '+@des1 else @empty end
+ case when (stats & 2)<>0 then ', '+@des2 else @empty end
+ case when (stats & 4)<>0 then ', '+@des4 else @empty end
+ case when (stats & 64)<>0 then ', '+@des64 else case when (stats & 32)<>0 then ', '+@des32 else @empty end end
+ case when (stats & 2048)<>0 then ', '+@des2048 else @empty end
+ case when (stats & 4096)<>0 then ', '+@des4096 else @empty end
+ case when (stats & 8388608)<>0 then ', '+@des8388608 else @empty end
+ case when (stats & 16777216)<>0 then ', '+@des16777216 else @empty end
+ ' located on ' + groupname)
from #spindtab
WHERE (STATS & 16) <> 0 AND (STATS & 2048) = 0
order by index_name
END
ELSE
IF @CLUST = 'NONCLUSTERED'
BEGIN
select
'Tablename' = @objname,
'Index_name' = index_name,
'Index_keys' = index_keys,
'ISClustered' = case when (stats & 16) <> 0 then 'YES' else 'NO' end,
'ISPrimary_Key'= case when (stats & 2048)<>0 then 'YES' else 'NO' end,
'IDXFileGroup' = groupname,
'index_description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group
case when (stats & 16)<>0 then 'clustered' else 'nonclustered' end
+ case when (stats & 1)<>0 then ', '+@des1 else @empty end
+ case when (stats & 2)<>0 then ', '+@des2 else @empty end
+ case when (stats & 4)<>0 then ', '+@des4 else @empty end
+ case when (stats & 64)<>0 then ', '+@des64 else case when (stats & 32)<>0 then ', '+@des32 else @empty end end
+ case when (stats & 2048)<>0 then ', '+@des2048 else @empty end
+ case when (stats & 4096)<>0 then ', '+@des4096 else @empty end
+ case when (stats & 8388608)<>0 then ', '+@des8388608 else @empty end
+ case when (stats & 16777216)<>0 then ', '+@des16777216 else @empty end
+ ' located on ' + groupname)
from #spindtab
WHERE (STATS & 16) = 0 AND (STATS & 2048) = 0
order by index_name
END
ELSE
BEGIN
select
'Tablename' = @objname,
'Index_name' = index_name,
'Index_keys' = index_keys,
'ISClustered' = case when (stats & 16) <> 0 then 'YES' else 'NO' end,
'ISPrimary_Key'= case when (stats & 2048)<>0 then 'YES' else 'NO' end,
'IDXFileGroup' = groupname,
'index_description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group
case when (stats & 16)<>0 then 'clustered' else 'nonclustered' end
+ case when (stats & 1)<>0 then ', '+@des1 else @empty end
+ case when (stats & 2)<>0 then ', '+@des2 else @empty end
+ case when (stats & 4)<>0 then ', '+@des4 else @empty end
+ case when (stats & 64)<>0 then ', '+@des64 else case when (stats & 32)<>0 then ', '+@des32 else @empty end end
+ case when (stats & 2048)<>0 then ', '+@des2048 else @empty end
+ case when (stats & 4096)<>0 then ', '+@des4096 else @empty end
+ case when (stats & 8388608)<>0 then ', '+@des8388608 else @empty end
+ case when (stats & 16777216)<>0 then ', '+@des16777216 else @empty end
+ ' located on ' + groupname)
from #spindtab WHERE (STATS & 2048) = 0
order by index_name
END
END
ELSE
BEGIN
IF @CLUST = 'CLUSTERED'
BEGIN
select
'Tablename' = @objname,
'Index_name' = index_name,
'Index_keys' = index_keys,
'ISClustered' = case when (stats & 16) <> 0 then 'YES' else 'NO' end,
'ISPrimary_Key'= case when (stats & 2048)<>0 then 'YES' else 'NO' end,
'IDXFileGroup' = groupname,
'index_description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group
case when (stats & 16)<>0 then 'clustered' else 'nonclustered' end
+ case when (stats & 1)<>0 then ', '+@des1 else @empty end
+ case when (stats & 2)<>0 then ', '+@des2 else @empty end
+ case when (stats & 4)<>0 then ', '+@des4 else @empty end
+ case when (stats & 64)<>0 then ', '+@des64 else case when (stats & 32)<>0 then ', '+@des32 else @empty end end
+ case when (stats & 2048)<>0 then ', '+@des2048 else @empty end
+ case when (stats & 4096)<>0 then ', '+@des4096 else @empty end
+ case when (stats & 8388608)<>0 then ', '+@des8388608 else @empty end
+ case when (stats & 16777216)<>0 then ', '+@des16777216 else @empty end
+ ' located on ' + groupname)
from #spindtab
WHERE (STATS & 16) <> 0
order by index_name
END
ELSE
IF @CLUST = 'NONCLUSTERED'
BEGIN
select
'Tablename' = @objname,
'Index_name' = index_name,
'Index_keys' = index_keys,
'ISClustered' = case when (stats & 16) <> 0 then 'YES' else 'NO' end,
'ISPrimary_Key'= case when (stats & 2048)<>0 then 'YES' else 'NO' end,
'IDXFileGroup' = groupname,
'index_description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group
case when (stats & 16)<>0 then 'clustered' else 'nonclustered' end
+ case when (stats & 1)<>0 then ', '+@des1 else @empty end
+ case when (stats & 2)<>0 then ', '+@des2 else @empty end
+ case when (stats & 4)<>0 then ', '+@des4 else @empty end
+ case when (stats & 64)<>0 then ', '+@des64 else case when (stats & 32)<>0 then ', '+@des32 else @empty end end
+ case when (stats & 2048)<>0 then ', '+@des2048 else @empty end
+ case when (stats & 4096)<>0 then ', '+@des4096 else @empty end
+ case when (stats & 8388608)<>0 then ', '+@des8388608 else @empty end
+ case when (stats & 16777216)<>0 then ', '+@des16777216 else @empty end
+ ' located on ' + groupname)
from #spindtab
WHERE (STATS & 16) = 0
order by index_name
END
ELSE
BEGIN
select
'Tablename' = @objname,
'Index_name' = index_name,
'Index_keys' = index_keys,
'ISClustered' = case when (stats & 16) <> 0 then 'YES' else 'NO' end,
'ISPrimary_Key'= case when (stats & 2048)<>0 then 'YES' else 'NO' end,
'IDXFileGroup' = groupname,
'index_description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group
case when (stats & 16)<>0 then 'clustered' else 'nonclustered' end
+ case when (stats & 1)<>0 then ', '+@des1 else @empty end
+ case when (stats & 2)<>0 then ', '+@des2 else @empty end
+ case when (stats & 4)<>0 then ', '+@des4 else @empty end
+ case when (stats & 64)<>0 then ', '+@des64 else case when (stats & 32)<>0 then ', '+@des32 else @empty end end
+ case when (stats & 2048)<>0 then ', '+@des2048 else @empty end
+ case when (stats & 4096)<>0 then ', '+@des4096 else @empty end
+ case when (stats & 8388608)<>0 then ', '+@des8388608 else @empty end
+ case when (stats & 16777216)<>0 then ', '+@des16777216 else @empty end
+ ' located on ' + groupname)
from #spindtab
order by index_name
END
END
return (0) -- sp_helpindex
drop table #spindtab
drop table #groupids
