Hi, today I'm looking for advice on how to manage a large number of similar   
   table indexes.  I may have asked before, but it didn't get done.   
      
   I have data to work on spread through many tables in many databases, with   
   names such as [BO-Stud18].[dbo].[Calendar_2012], where all "Calendar" tables   
   have the same structure, or more or less so.  Sometimes these tables are   
   dropped and re-created.  This isn't an ideal design, but I'm kind of stuck   
   with it.   
      
   Also, stored procedures that operate on the data tend to create an index   
   when the programmer has decided that it's needed.  But the indexes are not   
   necessarily well designed, and not necessarily appropriate, e.g. with   
   a unique key specified where it actually may not be unique in data.   
      
   Well, presently I'm considering putting all of the index definitions into   
   a huge stored procedure which will be called with parameters 'Calendar' and   
   the other parts of the table and database name separated out, and optionally   
   a particular index name, and in which, something like this happens:   
      
   IF ( @tableName = N'Calendar' )   
   BEGIN   
       IF ( @indexName = N'PK' OR @indexName IS NULL ) -- AND (1=0)   
       BEGIN   
           SET @template =   
           N'ALTER TABLE @{table} ADD CONSTRAINT @{index} PRIMARY KEY CLUSTERED   
           (authcode, id) --X';   
           SET @workstring =   
                   REPLACE(REPLACE(   
                             @template   
                           , N'@{table}', @tableName_fq)   
                           , N'@{index}', QUOTENAME(@indexNamePK))   
           ;   
           EXEC sp_executesql @workstring;   
       END   
      
       IF ( @indexName = N'IX02' OR @indexName IS NULL )   
       BEGIN   
           SET @template =   
           N'CREATE NONCLUSTERED INDEX IX02 ON @{table}   
           (id, expectedCurrentCostcentre) ON [INDEX] --X';   
           SET @workstring = REPLACE(@template, N'@{table}', @tableName_fq);   
           EXEC sp_executesql @workstring;   
       END   
      
       IF ( @indexName = N'IX03' OR @indexName IS NULL )   
       BEGIN   
           SET @template =   
           N'CREATE NONCLUSTERED INDEX IX03 ON @{table}   
           (expectedCurrentCostcentre) ON [INDEX] --X';   
           SET @workstring = REPLACE(@template, N'@{table}', @tableName_fq);   
           EXEC sp_executesql @workstring;   
       END   
   END   
      
   ELSE  -- a similar block for another index   
      
   When I want to change the design of indexes, I would put the new index   
   declaration in the procedure, and execute it for all of the tables that   
   should have that index.  And I go on executing it forever.   
      
   Since this will be a big, ugly program, one refinement that comes to mind   
   is to have the index definitions for each table held in a function named   
   after that table, instead.  Or, there may be a completely different good   
   way to do the job.   
      
   I don't want to go down the wrong path, I don't want to re-invent the wheel -   
   so, what do I want to know, but I don't know it?   
      
   Thanks!   
      
   Robert Carnegie   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)