T-SQL:: INDEX-중복인덱스 제거.

-- 퍼옴.
 

/*
 
 Find Duplicate Indexes
 
 This script returns the Duplicate indexes on any database
 I use 2 User defined Functions
 Compatible with SQLServer 2000 (I used sp3)
 It won't work in SQL Server 7.0 because It uses user-defined functions and a Memory.
 
 
 Created by G.R. Preethiviraj Kulasingham
pkulasingham@virtusa.com
 Written on  : February  20, 2003
 Modified on : May 18, 2004
Additional code written to remove duplicate entries.
Additional code to include index properties.
*/
 
IF EXISTS(Select id from sysobjects Where id = object_id('dbo.GetColID'))
   DROP FUNCTION dbo.getColID
GO
 
Create Function dbo.getColID (@TableID int, @indid int)
/*
Parameters:
@TableID: ID of the Table
@IndID  : ID of the Index
Returns All the Columns (ID) for the given index in string format seperated by '&'sign. '-' is added for descending keys
*/
 
Returns Varchar(200)
As
BEGIN
     Declare @SQL varchar(200)
     Set @SQL =''
     Select @SQL= @SQL +'&'+  convert(varchar(7),((indexkey_property(id, indid, keyno, 'isdescending')*-2)+1)* colid)   from sysindexkeys
     Where id =@Tableid and indid=@Indid
     Order by id, indid, Keyno
     IF LEN(@SQL)>1
          Select @SQL = @SQL +'&'
     Return @SQL
END
GO
 
 
IF EXISTS(Select id from sysobjects Where id = object_id('dbo.GetColList'))
   DROP FUNCTION dbo.getColList
 
GO
 
Create Function dbo.getColList (@Tableid int, @indid int)
/*
Parameters:
@TableID: ID of the Table
@IndID  : ID of the Index
Returns Index Key (column names) for the given index in string format seperated by commas.
*/
 
Returns Varchar(8000)
As
BEGIN
     Declare @SQL varchar(8000)
     Set @SQL =''
     Select @SQL= @SQL +', '+ INDEX_Col(User_name(ObjectProperty(@TableID, 'Ownerid'))+'.'+Object_name(@TableID), @indid, keyno)+
     Case indexkey_property(id, indid, keyno, 'isdescending') When 1 Then '(Desc)' Else '' end   from sysindexkeys
     Where id =@Tableid and indid=@Indid
     Order by id, indid, Keyno
     IF LEN(@SQL)>2
          Select @SQL = SUbString(@SQL, 3, LEN(@SQL)-2)
     Return @SQL
END
GO
 
 
/*
@TempTable is used to store the keys in long string format
*/
 
Declare @TempTable Table (
ID int ,
Indid int,
ColID Varchar(200),
Status int
)
 
Declare @Duplicates Table (
LineID int Identity(1,1),
ID int ,
hasClustered char(3) not null default('NO'),
Indid1 int,
ColID1 Varchar(200),
Status1 int,
Desc1 varchar(200),
IndID2 int,
ColID2 Varchar(200),
Status2 int,
Desc2 varchar(100)
)
 
Insert into @TempTable 
Select Id, indid, dbo.GetColid(id, indid), status  from Sysindexes
where (status & 64)=0 
order by id
Delete @TempTable Where ColID=''
 
 
Insert into @Duplicates (ID, IndID1, ColID1, Desc1, Status1, IndID2, ColID2, desc2, status2 )
Select A.ID, A.IndID,  A.ColID, '', A.status, B.IndID,  B.ColID, '', B.status
from @Temptable A, @TempTable B
Where A.id = b.id and a.indid<>b.indid and
     a.colid like b.colid
+'%'
 
 
--This part removes the duplicate entries.
 
Delete @Duplicates Where LineID In (
Select A.LineID from @Duplicates A, @Duplicates B
Where A.ID = B.ID and A.IndID1= B.IndID2 and A.IndID2= B.IndID1 and  A.LineID>B.LineID)
Delete @Duplicates Where LineID In (
Select A.LineID from @Duplicates A, @Duplicates B
Where A.ID = B.ID and  A.IndID1 = B.IndID2 )
 
-- Identify the index properties
 
Update @Duplicates Set Desc1 =CASE status1 & 16 WHEN 0 THEN 'Nonclustered' ELSE 'Clustered' END
Update @Duplicates Set Desc2 =CASE status2 & 16 WHEN 0 THEN 'Nonclustered' ELSE 'Clustered' END
 
 
 
Declare @Desc varchar(20), @Number int
Declare spt_Vals Cursor 
FOR
Select name, number  from master.dbo.spt_Values
Where type ='I' and number in (1,2, 4, 32, 2048, 4096) 
Order by number
Open  spt_vals
FETCH Next from spt_vals into @Desc, @Number
WHILE @@FETCH_STATUS=0
  BEGIN
Update @Duplicates Set Desc1 = Desc1 + ', '+ @Desc 
where status1 & @number <>0
Update @Duplicates Set Desc2 = Desc2 + ', '+ @Desc 
where  status2 & @number <>0
FETCH Next from spt_vals into @Desc, @Number
 END
 
CLOSE spt_Vals
DEALLOCATE spt_vals
Update @Duplicates Set  Desc1 = replace(Desc1, 'unique, primary key', 'primary key'), 
Desc2 = replace(Desc2, 'unique, primary key', 'primary key')
 
Update @Duplicates Set  Desc1 = replace(Desc1, 'unique, unique key', 'unique key'), 
Desc2 = replace(Desc2, 'unique, unique key', 'unique key')
 
-- Identify whether table has clustered index
Update @Duplicates Set HasClustered = 'YES' Where id in (
Select id From sysIndexes Where IndId=1)
 
--Update @Duplicates Set HasClustered = 'NO' Where id in (
--Select id From sysIndexes Where IndId=0)
 
 
 
Select User_name(ObjectProperty(A.ID, 'Ownerid'))+'.'+Object_name(a.id) 'Table Name', HasClustered,
IA.Name 'Index 1', dbo.GetColList(A.id, A.indid1) 'Keys of Index 1',  A.Desc1 'Desc 1',
IB.Name 'Index 2', dbo.GetColList(A.id, A.indid2) 'Columns of Index 2', A.Desc2 'Desc 2'
from @Duplicates A, SysIndexes IA, Sysindexes IB
Where IA.id =A.id and IA.indId = A.IndID1 and IB.ID = A.ID and IB.indId=A.IndID2
order by User_name(ObjectProperty(A.ID, 'Ownerid'))+'.'+Object_name(a.id)
 
/*
 
GO
DROP FUNCTION dbo.getColList
 
GO
DROP FUNCTION dbo.getColID
GO
*/