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

-- 퍼옴.
 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180

/*     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 */