December 23, 2010 at 8:03 am
Hi All
I am trying to automate some QC stuff on administration side and looking for any better thoughts. Our process is designed in such a way that few production database are pushed from Dev to Prod server by doing a backup and restore during which our ETL team will drop and re create some tables on Dev server by which we lose some indexes which has to be addressed by me before rolling out to production.
I am planning to create a master table which has all indexes with its definitions and I wanted a job that checks through all indexes and find the missging ones and email me with list of indexes on any database that has to be created or i would like the job to aumatically create those indexes if they are missed.
PLease through out your ideas and if you already have any scripts which does this please share.
December 28, 2010 at 8:17 am
does anyone have scripts ready to use for this purpose?
December 28, 2010 at 9:01 am
This might be a start. It will list all indexes on all tables, plus a bit more about the index:
select g.name, d.name as 'IndexName', e.indid as 'IndexID', e.keyno as 'KeyFldSeq',
f.name as 'FieldName'
from sysindexes d
join sysindexkeys e on d.id = e.id and d.indid = e.indid
join syscolumns f on f.id = d.id and f.colid = e.colid
join sysobjects g on g.id = d.id
and g.name NOT LIKE 'Sys%'
--where g.name = 'TableName' -- << ------- Optional TABLE NAME HERE
and d.name not like '_WA_Sys%'
order by g.name, e.indid, e.keyno
It should be a rather simple matter to run it on your Dev unit, and then on the production unit, with results going to a different tables in a common Admin db and then linking those 2 to find missing indexes. Although not the complete solution to your problem .. hopefully it will get your started.
December 28, 2010 at 9:19 am
is there a way i can get complete definition of index with list of all indexes in a database.?
December 28, 2010 at 9:33 am
I have a table with list of indexes which should be be present in 10 different databases (similar indexes), I would like to compare indexes on a database to this table every week and send an email with list of missing indexes. How can i do that?
December 28, 2010 at 9:58 am
I have not tested the code T-SQL from this article:
http://www.sqlservercentral.com/scripts/Miscellaneous/31092/
From above link:
This is an update to sp_helpindex, it allows you to pass the table name and whether you want to retrieve clustered, nonclustered or primary key indexes for a table. Using the sp_msforeach table will allow you to do this for all tables in a database. Inserting the data into a table will allow you to keep the information for reference purposes, or to recreate a lost index.
***Previous version did not account for multiple fiegroups, this one does, also includes script for recreation of indexes***
You may want to read the article and test the code ...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply