Procedimiento para encontrar índices duplicados en SQL Server
Greg Liwood escribió un procedimiento para detectar los índices duplicados en SQL Server, lo cual ayuda a mejorar el desempeño.
exec sp_MSForEachTable 'usp_duplicateindexes''?'''
Here's the proc script:
USE master
GO
create procedure [dbo].[usp_duplicateindexes]
@tablename varchar(255)
as
set nocount on
print @tablename
--dump sp_helpindex into temp table
if object_id('tempdb..#helpindex') > 0 drop table #helpindex
create table #helpindex (
index_name varchar (900) not null primary key
, index_description varchar (1000) null
, index_keys varchar (1000) null)
insert #helpindex exec sp_helpindex @tablename
--add [inccols] to temp table & cursor over output, adding included col defs
alter table #helpindex add inccols varchar(1000) null
declare cr cursor for
select si.name, sc.name
from sysobjects so
join sysindexes si on so.id = si.id
join sys.index_columns ic on si.id = ic.object_id and si.indid = ic.index_id
join sys.columns sc on ic.object_id = sc.object_id and ic.column_id = sc.column_id
where so.xtype = 'U'
and so.name = @tablename
and ic.is_included_column = 1
order by si.name, ic.index_column_id
declare @siname varchar(1000), @scname varchar(1000)
open cr
fetch next from cr into @siname, @scname
while @@fetch_status = 0
begin
update #helpindex set inccols = isnull(inccols , '') + @scname + ', ' where index_name = @siname
fetch next from cr into @siname, @scname
end
update #helpindex set inccols = left(inccols, datalength(inccols) - 2) where right(inccols, 2) = ', '
close cr
deallocate cr
--dump duplicates into second temp table & pump results if there are any
if object_id('tempdb..#helpindex2') > 0 drop table #helpindex2
create table #helpindex2 (
index_name varchar (900) not null primary key
, index_description varchar (1000) null
, index_keys varchar (1000) null
, inccols varchar(1000) null
)
insert into #helpindex2
select hi.index_name, hi.index_description, hi.index_keys, hi.inccols
from #helpindex hi
join #helpindex h2 on hi.index_keys=h2.index_keys
and hi.index_description=h2.index_description
and hi.index_name<>h2.index_name
if @@rowcount > 0
select @tablename as "tablename", * from #helpindex2 order by index_name, index_keys, inccols
--cleanup temp objects
if object_id('tempdb..#helpindex2') > 0 drop table #helpindex2
if object_id('tempdb..#helpindex') > 0 drop table #helpindex
Slds
Eduardo Castro - Comunidad Windows http://mswindowscr.org
Comments