Quick Fix secondary Indexes ( SQL 2000 )

,

In real world production tuning sometimes a "quick fix" is required. This script will generate index create scripts for any column ending ID which doesn't have an index. It's dirty and its quick but you might just be amazed by the results when run against your database!

--
-- Set to required database
--
use xxxxx
go
--
-- I've used a # temp table so the contents can be saved or viewed
-- if required
--
create table #test2(ObjName sysname,ColName sysname,ObjId int,ColId int)
--
insert into #test2(ObjName,ColName,ObjId,ColId)
select obj.name, col.name, obj.id,col.colid
from dbo.sysobjects obj join dbo.syscolumns col on obj.id=col.id
where col.name like '%id'
and obj.xtype='U'
--
-- remove where an index exists
--
delete from #test2 where convert(varchar,objid)+':'+convert(varchar,colid) in 
( select convert(varchar,id)+':'+convert(varchar,colid) from dbo.sysindexkeys )
--
-- put QA into text output
-- cut and paste output to create indexes
--
select 'create index idx_'+ObjName+'_'+colname+' on '+objname+'('+colname+')'+char(10)+'go'+char(10)
from #test2
--
-- once you're happy remove temp table
-- drop table #test2
--

Rate

Share

Share

Rate