I'm pursuing (at least mentally) the plan of using individual   
   stored procedures, for instance, to create or re-create some   
   simple indexes /and/ statistics on tables, so that the definitions   
   of these objects are in a single place.   
      
   In a database design which I know is bad, one table design, and   
   requirement for an index or statistics, is liable to exist in   
   multiple copies with a different database name, and a different   
   year number appended to the table name.  So the idea is to write a   
   procedure - example below - that can create the same index on   
   any copy of the table.   
      
   What I want to check with you is this: in SQL Server 2005,   
   am I OK to create similar indexes and statistics on several tables   
   using the same index and statistic names each time, /except/   
   that a primary key index / constraint name must be /unique/   
   in the database......or in the schema??   
      
   Here's an untested prototype procedure; there will be one for each   
   set of similar tables.  This is intended to be called with   
   @tableName_fq such as N'[BO-Stud15].[dbo].[ExpectedCostcentre_2011]',   
   @indexName probably NULL, 'PK', 'IX02', or 'IX03',  and   
   @indexNamePK = N'PK_ExpectedCostcentre_2011', to create   
   a database-unique-named primary key object.  Obviously, I'm   
   hoping to get away with using the same index and statistics names   
   for each table, except for the primary key.  In fact, I'm very fed up   
   with index names, as you see.   
      
   Yes, each database has a filegroup called INDEX to put indexes in.   
   And a dash in the database name.   
      
   I'm also not sure what happens if the table names are these:   
      
   N'[BO-Stud15].[year2011].[ExpectedCostcentre]'   
   N'[BO-Stud15].[year2012].[ExpectedCostcentre]'   
   N'[BO-Stud15].[year2013].[ExpectedCostcentre]'   
      
   Can I then use the same primary key name on each of these tables?   
      
   ----   
      
   CREATE PROCEDURE CreateIndex_Stud_ExpectedCostcentre   
             @tableName_fq sysname, @indexName sysname, @indexNamePK sysname   
   AS   
   DECLARE   
         @template    nvarchar(max)   
       , @workstring  nvarchar(max)   
      
      
   IF ( @indexName = N'PK' OR @indexName IS NULL )   
   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   
      
   GO   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)