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
| set transaction isolation level read uncommitted
set nocount on
select
'IF EXISTS(SELECT 1 FROM SYSINDEXES WHERE ID = OBJECT_ID('''+tab.name+''') AND NAME = '''+ind.name+''')
DROP INDEX ['+tab.name+'].['+ind.name+']
'
+ 'GO' + '
'
+ 'CREATE'
+ case when INDEXPROPERTY(ind.id, ind.name, 'IsUnique') = 1 then ' UNIQUE' else '' end
+ case when INDEXPROPERTY(ind.id, ind.name, 'IsClustered') = 1 then ' CLUSTERED' else ' NONCLUSTERED' end
+ ' INDEX ['+ind.name+']'
+ ' ON ['+user_name(tab.uid)+'].['+tab.name+']'
+ ' ('+
+ '['+index_col(tab.name, ind.indid, 1)+']'
+ isnull(', ['+index_col(tab.name, ind.indid, 2)+']', '')
+ isnull(', ['+index_col(tab.name, ind.indid, 3)+']', '')
+ isnull(', ['+index_col(tab.name, ind.indid, 4)+']', '')
+ isnull(', ['+index_col(tab.name, ind.indid, 5)+']', '')
+ isnull(', ['+index_col(tab.name, ind.indid, 6)+']', '')
+ isnull(', ['+index_col(tab.name, ind.indid, 7)+']', '')
+ isnull(', ['+index_col(tab.name, ind.indid, 8)+']', '')
+ isnull(', ['+index_col(tab.name, ind.indid, 9)+']', '')
+ isnull(', ['+index_col(tab.name, ind.indid, 10)+']', '')
+ isnull(', ['+index_col(tab.name, ind.indid, 11)+']', '')
+ isnull(', ['+index_col(tab.name, ind.indid, 12)+']', '')
+ isnull(', ['+index_col(tab.name, ind.indid, 13)+']', '')
+ isnull(', ['+index_col(tab.name, ind.indid, 14)+']', '')
+ isnull(', ['+index_col(tab.name, ind.indid, 15)+']', '')
+ isnull(', ['+index_col(tab.name, ind.indid, 16)+']', '')
+ ') WITH FILLFACTOR = ' + convert(varchar, case when ind.OrigFillFactor = 0 then 100 else ind.OrigFillFactor end)
+ case when INDEXPROPERTY(ind.id, ind.name, 'IsPadIndex') = 1 then ', PAD_INDEX' else '' end
+ case when ind.status & 1 = 1 then ', IGNORE_DUP_KEY' else '' end
+ ' ON ['+fg.groupname+']'+'
GO'+'
'
from sysindexes ind
join sysobjects tab on tab.id = ind.id
join sysfilegroups fg on fg.groupid = ind.groupid
where INDEXPROPERTY(tab.id, ind.name, 'IsStatistics') = 0
and tab.type = 'U'
and not exists(
select 1
from sysobjects pk
where pk.type = 'K'
and pk.parent_obj = tab.id
and pk.name = ind.name
)
order by tab.name, ind.indid |
Partager