一键生成库里索引脚本

if exists (select * from tempdb..sysobjects where name ='##T')
drop table ##T
create table ##T (name varchar(128),t varchar(1000),keys varchar(1000),tab varchar(100))

exec sp_MSforeachtable '

insert ##T(name,t,keys)
exec sp_helpindex ''?''
update ##T set tab=''?''
where tab is null
'

select
(CASE WHEN t LIKE '%primary key%'
THEN
'
begin try
ALTER TABLE '+tab+' DROP CONSTRAINT '+quotename(name)+'
end try
begin catch
print  ERROR_MESSAGE()
end catch
'
ELSE
'
begin try
if object_id('''+tab+''') is not null and
 exists(select 1 from sys.indexes
where name ='''+name+''')
drop  index '+quotename(name)+' on '+tab+'
end try
begin catch
print  ERROR_MESSAGE()
end catch
'END) del,
(CASE WHEN t LIKE '%primary key%'
THEN
'ALTER TABLE '+tab+' ADD PRIMARY KEY CLUSTERED
('+
replace(keys,'(-)',' desc ')
+')'else
'
if object_id('''+tab+''') is not null and
not exists(select 1 from sys.indexes
where name ='''+name+''')
create '+
replace(replace(left(t,len(t)-patindex('% on %',t)),',',' '),'noncluster','nonclustered')
+' index '+quotename(name)+' on '+tab+'('+
replace(keys,'(-)',' desc ')
+')
' END) ad

 from ##T

WHERE keys LIKE '%orderid%' OR keys LIKE '%ordercode%'
drop table ##T