Post reply

Indexes

  • November 21, 2010 at 10:52 am

    #147722

    I have nearly 20 databases which are compltely itenditical . I would like do some check every time i do some data loads into those databses so that i can check if all db's have all indexes that has to be. what would be the best idea?

    Here is what i thought.

    If I create a table with all table and index list, i should be able to check that against all db's, how do i do that for all db's in a single query which gives me a list of indexs that are missing across all 20 db's ?

  • November 21, 2010 at 11:24 am

    #1253511

    Have you considered using sp_MSForEachDB? Here is a variation on accomplishing the same effect

    http://www.sqlservercentral.com/Forums/Topic705561-146-1.aspx

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • November 21, 2010 at 6:05 pm

    #1253547

    Tara-1044200 (11/21/2010)

    I have nearly 20 databases which are compltely itenditical . I would like do some check every time i do some data loads into those databses so that i can check if all db's have all indexes that has to be. what would be the best idea?

    Here is what i thought.

    If I create a table with all table and index list, i should be able to check that against all db's, how do i do that for all db's in a single query which gives me a list of indexs that are missing across all 20 db's ?

    Ron's suggestion is a good start. I'll also add that you can forget about doing it all in a single query because it's just not possible unless you hardcode "dips" to sys.objects for all 20 databases.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 22, 2010 at 4:05 am

    #1253650

    If you have a copy of REDGATE SQLCOMPARE you can use the API for this to compare each database to a "template database" you can then have it put this data out to a spreadsheet (or reporting services report) to show you the state of each index/object in the form of a traffic(esque) light chart.

    green=same

    amber=different

    red=missing

    black=object in database but not in template

    I have some code that does this, but it's dependent on you having a copy of sql compare, and also some good c# skills as it's hard coded for my environment at the minute

    MVDBA

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply