Technical Article

Remove index fragmentation

,

This script will drop and recreated all indexes in a database within a transaction. The script will also add standard nameing conventions to the index names. Any duplicated indexes are removed.

/**********************************************************************************************
      Author: Andrew J. Esser                                                                 *
 Create Date: April-25-2006                                                                   *
***********************************************************************************************
 Update Date: April-28-2006 - A.J.E.                                                          *
   Fixed error of duplicated indexes being created. Problem occured when two tables with the  *
   same name but different owners were present. Filtered base table name selection to only    *
   reindex tables that are owned by 'dbo'.                                                    *
                                                                                              *
   Fixed issue of duplicated index definitions not being dropped corectly.                    *
***********************************************************************************************
   Purpose:                                                                                   *
   1). Drop and recreate all indexes in the current database which are user-defined.          *
   2). Standardize nameing conventions for index creation.                                    *
   3.) Remove duplicated index entries.                                                       *
***********************************************************************************************
   For @tbl table variable, the TypeOf char 2 field values mean the following:                *
***********************************************************************************************
   1). 'FK' = Foriegn Key                                                                     *
   2). 'DF' = Default Constraint                                                              *
   3). 'IX' = Index            - Non-unique                                                   *
   4). 'IU' = Index            - Unique                                                       *
   5). 'CL' = Clustered Index  - Non-unique                                                   *
   6). 'CU' = Clustered Index  - Unique                                                       *
   7). 'CX' = Constraint Index - Non-clustered                                                *
   8). 'CC' = Constraint Index - Clustered                                                    *
   9). 'PK' = Primary Key      - Non-clustered                                                *
  10). 'PC' = Primary Key      - Clustered                                                    *
***********************************************************************************************
   To drop all database indexes properly, the following order must be used for success.       *
***********************************************************************************************
   1). Drop Foriegn Keys                                                                      *
   2). Drop Default Constraints                                                               *
   3). Drop Indexes           - Non-unique                                                    *
   4). Drop Indexes           - Unique                                                        *
   5). Drop Clustered Indexes - Non-unique                                                    *
   6). Drop Clustered Indexes - Unique                                                        *
   7). Drop Index Constraints - Non-clustered                                                 *
   8). Drop Index Constraints - Clustered                                                     *
   9). Drop Primary Keys      - Non-clustered                                                 *
  10). Drop Primary Keys      - Clustered                                                     *
***********************************************************************************************
   To re-create database indexes properly, the following order must be used for success.      *
***********************************************************************************************
   1). Create Primary Keys       - Clustered                                                  *
   2). Create Primary Keys       - Non-clustered                                              *
   3). Create Constraint Indexes - Clustered                                                  *
   4). Create Constraint Indexes - Non-clustered                                              *
   5). Create Clustered Indexes  - Unique                                                     *
   6). Create Clustered Indexes  - Non-unique                                                 *
   7). Create Indexes            - Unique                                                     *
   8). Create Indexes            - Non-unique                                                 *
   9). Create Default Constraints                                                             *
  10). Create Foriegn Keys                                                                    *
**********************************************************************************************/
-- The first action is to get the foreign key references
-- on each table to build the drop and create scripts.
DECLARE @pkTABLE_name      sysname
      , @fkTABLE_name      sysname
      , @fkey              sysname
      , @fkey_orig         sysname
      , @valI              varchar(3000)
      , @valD              varchar(3000)
      , @fld_pk            varchar(1000)
      , @fld_fk            varchar(1000)
      , @pk_tbl            varchar(100)
      , @pkfull_TABLE_name nvarchar(257) /* 2*128 + 1 */      , @fkfull_TABLE_name nvarchar(257) /* 2*128 + 1 */      , @cnstdes   nvarchar(4000) -- string to build default index value.
      , @pkTABLE_id   int
      , @cnt               int
      , @cnt_of            int
      , @min_id            int
      , @tbl_cnt           int
      , @tbl_idx           int
      , @fkTABLE_id   int
      , @order_by_pk   int
      , @i                 int
   
   SET NOCOUNT ON
   
-- Virtual table pointer to contain all the tables in the current database.
DECLARE @tbl_all TABLE
      ( CountID  int     NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED
      , tbl_name sysname NOT NULL
      )
   
-- Virtual table pointer to contain raw foreign key column references.
DECLARE @fkeysall TABLE
      ( CountID int NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED
      , rkeyid  int NOT NULL
      , rkey1   int NOT NULL
      , rkey2   int NOT NULL
      , rkey3   int NOT NULL
      , rkey4   int NOT NULL
      , rkey5   int NOT NULL
      , rkey6   int NOT NULL
      , rkey7   int NOT NULL
      , rkey8   int NOT NULL
      , rkey9   int NOT NULL
      , rkey10  int NOT NULL
      , rkey11  int NOT NULL
      , rkey12  int NOT NULL
      , rkey13  int NOT NULL
      , rkey14  int NOT NULL
      , rkey15  int NOT NULL
      , rkey16  int NOT NULL
      , fkeyid  int NOT NULL
      , fkey1   int NOT NULL
      , fkey2   int NOT NULL
      , fkey3   int NOT NULL
      , fkey4   int NOT NULL
      , fkey5   int NOT NULL
      , fkey6   int NOT NULL
      , fkey7   int NOT NULL
      , fkey8   int NOT NULL
      , fkey9   int NOT NULL
      , fkey10  int NOT NULL
      , fkey11  int NOT NULL
      , fkey12  int NOT NULL
      , fkey13  int NOT NULL
      , fkey14  int NOT NULL
      , fkey15  int NOT NULL
      , fkey16  int NOT NULL
      , constid int NOT NULL
      , name    sysname COLLATE database_default NOT NULL
      )
   
-- Virtual table pointer for isolation to one foreign key field per
-- row to build the create and delete foreign key SQL statements.
DECLARE @fkeys TABLE
      ( CountID    int      NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED
      , pkTABLE_id int      NOT NULL
      , pkcolid    int      NOT NULL
      , fkTABLE_id int      NOT NULL
      , fkcolid    int      NOT NULL
      , KEY_SEQ    smallint NOT NULL
      , fk_id      int      NOT NULL
      , PK_NAME    sysname collate database_default NOT NULL
      )
   
DECLARE @fkeysout TABLE
      ( CountID           int NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED
      , PKTABLE_QUALIFIER sysname collate database_default NULL
      , PKTABLE_OWNER     sysname collate database_default NULL
      , PKTABLE_NAME      sysname collate database_default NOT NULL
      , PKCOLUMN_NAME     sysname collate database_default NOT NULL
      , FKTABLE_QUALIFIER sysname collate database_default NULL
      , FKTABLE_OWNER     sysname collate database_default NULL
      , FKTABLE_NAME      sysname collate database_default NOT NULL
      , FKCOLUMN_NAME     sysname collate database_default NOT NULL
      , KEY_SEQ           smallint NOT NULL
      , UPDATE_RULE       smallint NULL
      , DELETE_RULE       smallint NULL
      , FK_NAME           sysname collate database_default NULL
      , PK_NAME           sysname collate database_default NULL
      )
   
DECLARE @parse TABLE
      ( CountID int NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED
      , PKTABLE_QUALIFIER sysname collate database_default NULL
      , PKTABLE_OWNER     sysname collate database_default NULL
      , PKTABLE_NAME      sysname collate database_default NOT NULL
      , PKCOLUMN_NAME     sysname collate database_default NOT NULL
      , FKTABLE_QUALIFIER sysname collate database_default NULL
      , FKTABLE_OWNER     sysname collate database_default NULL
      , FKTABLE_NAME      sysname collate database_default NOT NULL
      , FKCOLUMN_NAME     sysname collate database_default NOT NULL
      , KEY_SEQ           smallint NOT NULL
      , UPDATE_RULE       smallint NULL
      , DELETE_RULE       smallint NULL
      , FK_NAME           sysname collate database_default NULL
      , PK_NAME           sysname collate database_default NULL
      )
   
DECLARE @tbl TABLE
      ( CountID         int           NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED
      , CreateStatement varchar(3000) NOT NULL
      , DropStatement   varchar(3000) NOT NULL
      , ConstraintName  varchar(128)  NOT NULL
      , OrigConstraint  varchar(128)  NOT NULL
      , TypeOf          varchar(2)    NOT NULL
      )
   
   IF (@table_name = N'all')
-- Insert the list of user table names contained in the current database.
      INSERT INTO @tbl_all
                ( tbl_name
                )
         SELECT TABLE_NAME
           FROM INFORMATION_SCHEMA.TABLES
          WHERE (TABLE_TYPE           = 'base table')
            AND (TABLE_NAME          <> 'dtproperties')
            AND (LEFT(TABLE_NAME, 4) <> 'Fog_')
            AND (LEFT(TABLE_NAME, 5) <> 'Spot_')
            AND (TABLE_SCHEMA         = 'dbo')
   ELSE
      INSERT INTO @tbl_all
                ( tbl_name
                )
         VALUES ( @table_name
                )
   
   SELECT @tbl_cnt = COUNT(*)
     FROM @tbl_all
   
   SET @tbl_idx = 1
   SET @fkTABLE_name = NULL
   
   WHILE (@tbl_idx <= @tbl_cnt)
   BEGIN
      SELECT @pkTABLE_name = tbl_name
        FROM @tbl_all
       WHERE (CountID = @tbl_idx)
      
-- Starting parameter analysis.
      SET @order_by_pk = 0
      
   IF (@pkTABLE_name IS NULL) AND (@fkTABLE_name IS NULL)
      BEGIN/* IF neither primary key nor foreign key TABLE names given */      RAISERROR (15252,-1,-1)
      RETURN
         END
      
-- Get prime key table Object ID.
   IF (@pkTABLE_name IS NOT NULL)
         BEGIN
         SET @pkfull_TABLE_name = quotename(@pkTABLE_name)
      SET @pkTABLE_id = object_id(@pkfull_TABLE_name)
   END
      
-- Get foreign key Object ID
      IF (@fkTABLE_name IS NOT NULL)
         BEGIN
            SET @fkfull_TABLE_name = quotename(@fkTABLE_name)
            SET @fkTABLE_id = object_id(@fkfull_TABLE_name)
         END
      
      IF (@fkTABLE_name IS NOT NULL)
      BEGIN
      IF (@fkTABLE_id IS NULL)
      SET @fkTABLE_id = 0-- fk TABLE NOT found, empty result.
         END
      
      IF (@pkTABLE_name IS NULL)
         SET @order_by_pk = 1
   ELSE
         BEGIN
            IF @pkTABLE_id IS NULL
         SELECT @pkTABLE_id = 0/* pk TABLE NOT found, empty result */      END
      
-- SQL Server supports upto 16 PK/FK relationships BETWEEN 2 TABLEs
-- Process syskeys for each relationship
/*  First, attempt to get all 16 keys for each rel'ship, then sort
them out with a 16-way "insert SELECT ... union SELECT ..." */-- Starting data analysis.
      INSERT INTO @fkeysall
                ( rkeyid
                , rkey1
                , rkey2
                , rkey3
                , rkey4
                , rkey5
                , rkey6
                , rkey7
                , rkey8
                , rkey9
                , rkey10
                , rkey11
                , rkey12
                , rkey13
                , rkey14
                , rkey15
                , rkey16
          , fkeyid
                , fkey1
                , fkey2
                , fkey3
                , fkey4
                , fkey5
                , fkey6
                , fkey7
                , fkey8
                , fkey9
                , fkey10
                , fkey11
                , fkey12
                , fkey13
                , fkey14
                , fkey15
                , fkey16
          , constid
   , name
                )
           SELECT r.rkeyid
                , r.rkey1
                , r.rkey2
                , r.rkey3
                , r.rkey4
                , r.rkey5
                , r.rkey6
                , r.rkey7
                , r.rkey8
                , r.rkey9
                , r.rkey10
                , r.rkey11
                , r.rkey12
                , r.rkey13
                , r.rkey14
                , r.rkey15
                , r.rkey16
                , r.fkeyid
                , r.fkey1
                , r.fkey2
                , r.fkey3
                , r.fkey4
                , r.fkey5
                , r.fkey6
                , r.fkey7
                , r.fkey8
                , r.fkey9
                , r.fkey10
                , r.fkey11
                , r.fkey12
                , r.fkey13
                , r.fkey14
                , r.fkey15
                , r.fkey16
                , r.constid
                , i.name
             FROM sysreferences r
                , sysobjects o
                , sysindexes i
            WHERE r.constid = o.id
              AND o.xtype = 'F '
              AND r.rkeyindid = i.indid
              AND r.rkeyid = i.id
              AND r.rkeyid BETWEEN isNULL(@pkTABLE_id, 0)
                               AND isNULL(@pkTABLE_id, 0x7fffffff)
              AND r.fkeyid BETWEEN isNULL(@fkTABLE_id, 0)
                               AND isNULL(@fkTABLE_id, 0x7fffffff)
      
      SELECT @cnt_of = COUNT(*)
        FROM @fkeysall
      
      IF (ISNULL(@cnt_of, 0) = 0)
         GOTO No_Foreign_Keys
      
      INSERT INTO @fkeys
                ( pkTABLE_id
                , pkcolid
                , fkTABLE_id
                , fkcolid
                , KEY_SEQ
                , fk_id
                , PK_NAME
                )
            SELECT rkeyid, rkey1, fkeyid, fkey1, 1, constid, name
              FROM @fkeysall
         UNION ALL
       SELECT rkeyid, rkey2, fkeyid, fkey2, 2, constid, name
              FROM @fkeysall
         UNION ALL
    SELECT rkeyid, rkey3, fkeyid, fkey3, 3, constid, name
      FROM @fkeysall
         UNION ALL
       SELECT rkeyid, rkey4, fkeyid, fkey4, 4, constid, name
      FROM @fkeysall
         UNION ALL
    SELECT rkeyid, rkey5, fkeyid, fkey5, 5, constid, name
      FROM @fkeysall
         UNION ALL
    SELECT rkeyid, rkey6, fkeyid, fkey6, 6, constid, name
      FROM @fkeysall
         UNION ALL
    SELECT rkeyid, rkey7, fkeyid, fkey7, 7, constid, name
      FROM @fkeysall
         UNION ALL
    SELECT rkeyid, rkey8, fkeyid, fkey8, 8, constid, name
      FROM @fkeysall
         UNION ALL
    SELECT rkeyid, rkey9, fkeyid, fkey9, 9, constid, name
      FROM @fkeysall
         UNION ALL
    SELECT rkeyid, rkey10, fkeyid, fkey10, 10, constid, name
      FROM @fkeysall
         UNION ALL
    SELECT rkeyid, rkey11, fkeyid, fkey11, 11, constid, name
      FROM @fkeysall
         UNION ALL
    SELECT rkeyid, rkey12, fkeyid, fkey12, 12, constid, name
      FROM @fkeysall
         UNION ALL
    SELECT rkeyid, rkey13, fkeyid, fkey13, 13, constid, name
      FROM @fkeysall
         UNION ALL
    SELECT rkeyid, rkey14, fkeyid, fkey14, 14, constid, name
      FROM @fkeysall
         UNION ALL
    SELECT rkeyid, rkey15, fkeyid, fkey15, 15, constid, name
      FROM @fkeysall
         UNION ALL
    SELECT rkeyid, rkey16, fkeyid, fkey16, 16, constid, name
      FROM @fkeysall
      
      DELETE
        FROM @fkeysall
      
      INSERT INTO @fkeysout
                ( PKTABLE_QUALIFIER
                , PKTABLE_OWNER
                , PKTABLE_NAME
                , PKCOLUMN_NAME
                , FKTABLE_QUALIFIER
                , FKTABLE_OWNER
                , FKTABLE_NAME
                , FKCOLUMN_NAME
                , KEY_SEQ
                , UPDATE_RULE
                , DELETE_RULE
                , FK_NAME
                , PK_NAME
                )
           SELECT PKTABLE_QUALIFIER = convert(sysname,db_name())
                , PKTABLE_OWNER = convert(sysname,USER_NAME(o1.uid))
                , PKTABLE_NAME = convert(sysname,o1.name)
                , PKCOLUMN_NAME = convert(sysname,c1.name)
                , FKTABLE_QUALIFIER = convert(sysname,db_name())
                , FKTABLE_OWNER = convert(sysname,USER_NAME(o2.uid))
                , FKTABLE_NAME = convert(sysname,o2.name)
                , FKCOLUMN_NAME = convert(sysname,c2.name)
                , KEY_SEQ
                , UPDATE_RULE = CASE WHEN (ObjectProperty(fk_id, 'CnstIsUpdateCascade')=1) THEN 
                            convert(smallint,0)
                                     ELSE
                                        convert(smallint,1)
                                END
                , DELETE_RULE = CASE WHEN (ObjectProperty(fk_id, 'CnstIsDeleteCascade')=1) THEN 
                            convert(smallint,0)
                                     ELSE
                                        convert(smallint,1)
                                END
                , FK_NAME = convert(sysname,OBJECT_NAME(fk_id))
                , PK_NAME
       FROM @fkeys f, sysobjects o1
                          , sysobjects o2
                          , syscolumns c1
                          , syscolumns c2
      WHERE o1.id = f.pkTABLE_id
     AND o2.id = f.fkTABLE_id
     AND c1.id = f.pkTABLE_id
     AND c2.id = f.fkTABLE_id
     AND c1.colid = f.pkcolid
     AND c2.colid = f.fkcolid
      
      DELETE
        FROM @fkeys
      
      SELECT @cnt = COUNT(*)
        FROM @fkeysout
      
      WHILE (@cnt > 0)
      BEGIN
         SELECT @fkey = FK_NAME
           FROM @fkeysout
          WHERE CountID = (SELECT MIN(CountID) FROM @fkeysout)
         
         INSERT INTO @parse
              ( PKTABLE_QUALIFIER
              , PKTABLE_OWNER
              , PKTABLE_NAME
              , PKCOLUMN_NAME
              , FKTABLE_QUALIFIER
              , FKTABLE_OWNER
              , FKTABLE_NAME
              , FKCOLUMN_NAME
              , KEY_SEQ
              , UPDATE_RULE
              , DELETE_RULE
              , FK_NAME
              , PK_NAME
              )
         SELECT PKTABLE_QUALIFIER
              , PKTABLE_OWNER
              , PKTABLE_NAME
              , PKCOLUMN_NAME
              , FKTABLE_QUALIFIER
              , FKTABLE_OWNER
              , FKTABLE_NAME
              , FKCOLUMN_NAME
              , KEY_SEQ
              , UPDATE_RULE
              , DELETE_RULE
              , FK_NAME
              , PK_NAME 
           FROM @fkeysout
          WHERE FK_NAME = @fkey
         
         SELECT @cnt_of = COUNT(*)
              , @min_id = MIN(CountID)
           FROM @parse
         
         IF (@cnt_of > 0)
            SELECT @valI = 'ALTER TABLE ['
                         + FKTABLE_OWNER + '].[' + FKTABLE_NAME + ']'
                         + ' ADD CONSTRAINT ['  + FK_NAME
                         + '] FOREIGN KEY ('
                 , @valD = 'ALTER TABLE ['
                         + FKTABLE_OWNER + '].[' + FKTABLE_NAME + ']'
                         + ' DROP CONSTRAINT [' + FK_NAME + ']'
                 , @pk_tbl = '[' + PKTABLE_OWNER + '].[' + PKTABLE_NAME + ']'
                 , @fkey      = FK_NAME
                 , @fkey_orig = FK_NAME
              FROM @parse
             WHERE CountID = @min_id
         
         SET @fld_fk = ''
         SET @fld_pk = ''
         
         WHILE (ISNULL(@cnt_of, 0) > 0)
         BEGIN
            IF (@fld_fk = '')
               SELECT @fld_fk = '[' + FKCOLUMN_NAME + ']'
                    , @fld_pk = '[' + PKCOLUMN_NAME + ']'
                 FROM @parse
                WHERE CountID = @min_id
            ELSE
               SELECT @fld_fk = (@fld_fk + ', [' + FKCOLUMN_NAME + ']')
                    , @fld_pk = (@fld_pk + ', [' + PKCOLUMN_NAME + ']')
                 FROM @parse
                WHERE CountID = @min_id
            
            DELETE
              FROM @parse
             WHERE CountID = @min_id
            
            SELECT @cnt_of = COUNT(*)
                 , @min_id = MIN(CountID)
              FROM @parse
         END -- of WHILE (@cnt_of > 0)
         
         SELECT @valI = @valI + @fld_fk + ') REFERENCES '
                              + @pk_tbl + '('
                              + @fld_pk + ')'
         
         INSERT INTO @tbl
                   ( CreateStatement
                   , DropStatement
                   , ConstraintName
                   , OrigConstraint
                   , TypeOf
                   )
            VALUES ( @valI
                   , @valD
                   , @fkey
                   , @fkey_orig
                   , 'FK'
                   )
         DELETE
           FROM @parse
         
         DELETE
         FROM @fkeysout
         WHERE FK_NAME = @fkey
         
         SELECT @cnt = COUNT(*)
           FROM @fkeysout
         
      END
      
No_Foreign_Keys:
      
      SET @tbl_idx = @tbl_idx + 1
   END -- of WHILE (@tbl_idx <= @tbl_cnt)
   
-- The next action is to build the drop and create SQL
-- for the default constraints.
DECLARE @tbl_def TABLE
      ( CountOf           int            NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED
      , Parent            sysname        NOT NULL
      , ConstraintName    sysname        NOT NULL
      , NewConstraintName sysname        NOT NULL
      , ConstraintField   sysname        NOT NULL
      , ConstraintValue   varchar(4000)  NOT NULL
      )
   
   SET @tbl_idx = 1
   
   WHILE (@tbl_idx <= @tbl_cnt)
   BEGIN
      SELECT @pkTABLE_name = tbl_name
           , @pkTABLE_id   = object_id(tbl_name)
        FROM @tbl_all
       WHERE (CountID = @tbl_idx)
      
      INSERT INTO @tbl_def
           ( Parent
           , ConstraintName
           , NewConstraintName
           , ConstraintField
           , ConstraintValue
           )
      SELECT '[' + convert(sysname,USER_NAME(so.uid)) + '].[' + so.name + ']' -- Parent table name.
           , sysobjects.name                                            -- Constraint name.
           , 'DF_'
             + REPLACE(so.name, ' ', '_')
             + '_'
             + REPLACE(col_name(@pkTABLE_id, sysobjects.info), ' ', '_') -- New Constraint name.
           , col_name(@pkTABLE_id, sysobjects.info)                      -- Constraint field name.
           , syscomments.text                                            -- Constraint value.
        FROM sysobjects INNER JOIN syscomments
                           ON sysobjects.id = syscomments.id
                        INNER JOIN sysobjects so
                           ON sysobjects.parent_obj = so.id
       WHERE sysobjects.parent_obj = @pkTABLE_id
   AND sysobjects.xtype = 'D '
         AND sysobjects.uid   = user_id(N'dbo')
         AND syscomments.colid = 1
      
      SET @tbl_idx = @tbl_idx + 1
   END -- of WHILE (@tbl_idx <= @tbl_cnt)
   
   SELECT @cnt = COUNT(*)
     FROM @tbl_def
   
   SET @i = 1
   
   IF (@cnt = 0)
      GOTO No_Default_Keys
   
   WHILE (@i <= @cnt)
   BEGIN
      SELECT @valI = ( 'ALTER TABLE '
                     + Parent
                     + ' ADD CONSTRAINT ['
                     + REPLACE(NewConstraintName, '__', '_')
                     + '] DEFAULT '
                     + ConstraintValue
                     + ' FOR [' + ConstraintField + ']'
                     )
           , @valD = ( 'ALTER TABLE '
                     + Parent
                     + ' DROP CONSTRAINT ['
                     + ConstraintName
                     + ']'
                     )
           , @fkey = REPLACE(NewConstraintName, '__', '_')
           , @fkey_orig = ConstraintName
        FROM @tbl_def
       WHERE (CountOf = @i)
      
      INSERT INTO @tbl
                ( CreateStatement
                , DropStatement
                , ConstraintName
                , OrigConstraint
                , TypeOf
                )
         VALUES ( @valI
                , @valD
                , @fkey
                , @fkey_orig
                , 'DF'
                )
      
No_Default_Keys:
      SET @i = @i + 1
   END -- of WHILE (@i <= @cnt)
   
-- Now create the Drop and Create statements for the regular indexes.
   INSERT INTO @tbl
             ( CreateStatement
             , DropStatement
             , ConstraintName
             , OrigConstraint
             , TypeOf
             )
        SELECT 'CREATE NONCLUSTERED INDEX [IX_'
             + o.name
             + '_'
             + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1)
             + CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
                       ''
                    ELSE
                       '_Ect'
               END
             + '] ON ['
             + u.name
             + '].['
             + o.name
             + ']([' 
             + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1) + ']'
             + CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 3) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 3) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 4) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name),i.indid, 4) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 5) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 5) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 6) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 6) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 7) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 7) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 8) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 8) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 9) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 9) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 10) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 10) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 11) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 11) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 12) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 12) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 13) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 13) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 14) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 14) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 15) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 15) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 16) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name),i.indid, 16) + ']'
               END +
               ') WITH FILLFACTOR = 90'
             , 'DROP INDEX ['
             + u.name
             + '].['
             + o.name
             + '].['
             + i.name
             + ']'
          , 'IX_'
             + o.name
             + '_'
             + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1)
             + CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
                       ''
                    ELSE
                       '_Ect'
               END
          , i.name
          , 'IX'
     FROM sysobjects o WITH (NOLOCK) LEFT OUTER JOIN sysindexes i WITH (NOLOCK)
                                  ON o.id = i.id
                               INNER JOIN sysusers u WITH (NOLOCK)
                                  ON o.uid = u.uid
    WHERE (o.type = 'U')
      AND (i.indid < 255)
      AND (i.indid > 1)
      AND (i.status & 2048) = 0
      AND (i.status & 4096) = 0
      AND (o.name <> 'dtproperties')
      AND (LEFT(o.name, 4) <> 'Fog_')
      AND (LEFT(o.name, 5) <> 'Spot_')
      AND o.uid   = user_id(N'dbo')
      AND (i.status & (2 | 4096)) = 0
      AND (LEFT(i.name, 8) <> '_WA_Sys_')
 ORDER BY o.name, i.indid
   
-- Now create the Drop and Create statements for the unique regular indexes.
   INSERT INTO @tbl
             ( CreateStatement
             , DropStatement
             , ConstraintName
             , OrigConstraint
             , TypeOf
             )
        SELECT 'CREATE UNIQUE NONCLUSTERED INDEX [IU_'
             + o.name
             + '_'
             + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1)
             + CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
                       ''
                    ELSE
                       '_Ect'
               END
             + '] ON ['
             + u.name
             + '].['
             + o.name
             + ']([' 
             + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1) + ']'
             + CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 3) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 3) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 4) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name),i.indid, 4) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 5) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 5) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 6) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 6) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 7) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 7) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 8) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 8) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 9) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 9) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 10) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 10) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 11) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 11) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 12) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 12) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 13) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 13) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 14) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 14) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 15) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 15) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 16) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name),i.indid, 16) + ']'
               END +
               ') WITH FILLFACTOR = 90'
             , 'DROP INDEX ['
             + u.name
             + '].['
             + o.name
             + '].['
             + i.name
             + ']'
          , 'IU_'
             + o.name
             + '_'
             + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1)
             + CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
                       ''
                    ELSE
                       '_Ect'
               END
          , i.name
          , 'IU'
     FROM sysobjects o WITH (NOLOCK) LEFT OUTER JOIN sysindexes i WITH (NOLOCK)
                                  ON o.id = i.id
                               INNER JOIN sysusers u WITH (NOLOCK)
                                  ON o.uid = u.uid
    WHERE (o.type = 'U')
      AND (i.indid < 255)
      AND (i.indid > 1)
      AND (i.status & 2048) = 0
      AND (i.status & 4096) = 0
      AND (o.name <> 'dtproperties')
      AND o.uid   = user_id(N'dbo')
      AND (LEFT(o.name, 4) <> 'Fog_')
      AND (LEFT(o.name, 5) <> 'Spot_')
      AND (i.status & (2 | 4096)) > 0
      AND (LEFT(i.name, 8) <> '_WA_Sys_')
 ORDER BY o.name, i.indid
   
-- Now create the Drop and Create statements for the non-unique clustered indexes.
   INSERT INTO @tbl
             ( CreateStatement
             , DropStatement
             , ConstraintName
             , OrigConstraint
             , TypeOf
             )
        SELECT 'CREATE CLUSTERED INDEX [CL_'
             + o.name
             + '_'
             + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1)
             + CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
                       ''
                    ELSE
                       '_Ect'
               END
             + '] ON ['
             + u.name
             + '].['
             + o.name
             + ']([' 
             + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1) + ']'
               +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 3) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 3) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 4) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name),i.indid, 4) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 5) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 5) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 6) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 6) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 7) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 7) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 8) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 8) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 9) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 9) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 10) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 10) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 11) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 11) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 12) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 12) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 13) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 13) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 14) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 14) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 15) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 15) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 16) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name),i.indid, 16) + ']'
               END +
               ') WITH FILLFACTOR = 90'
             , 'DROP INDEX ['
             + u.name
             + '].['
             + o.name
             + '].['
             + i.name
             + ']'
          , 'CL_'
             + o.name
             + '_'
             + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1)
             + CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
                       ''
                    ELSE
                       '_Ect'
               END
          , i.name
          , 'CL'
     FROM sysobjects o WITH (NOLOCK) LEFT OUTER JOIN sysindexes i WITH (NOLOCK)
                                  ON o.id = i.id
                               INNER JOIN sysusers u WITH (NOLOCK)
                                  ON o.uid = u.uid
    WHERE (o.type = 'U')
      AND (i.indid < 255)
      AND (i.indid = 1)
      AND (i.status & 2048) = 0
      AND (i.status & 4096) = 0
      AND (o.name <> 'dtproperties')
      AND o.uid   = user_id(N'dbo')
      AND (LEFT(o.name, 4) <> 'Fog_')
      AND (LEFT(o.name, 5) <> 'Spot_')
      AND (i.status & (2 | 4096)) = 0
      AND (LEFT(i.name, 8) <> '_WA_Sys_')
 ORDER BY o.name, i.indid
   
-- Now create the Drop and Create statements for the unique clustered indexes.
   INSERT INTO @tbl
             ( CreateStatement
             , DropStatement
             , ConstraintName
             , OrigConstraint
             , TypeOf
             )
        SELECT 'CREATE UNIQUE CLUSTERED INDEX [CU_'
             + o.name
             + '_'
             + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1)
             + CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
                       ''
                    ELSE
                       '_Ect'
               END
             + '] ON ['
             + u.name
             + '].['
             + o.name
             + ']([' 
             + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1) + ']'
               +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 3) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 3) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 4) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name),i.indid, 4) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 5) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 5) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 6) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 6) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 7) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 7) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 8) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 8) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 9) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 9) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 10) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 10) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 11) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 11) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 12) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 12) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 13) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 13) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 14) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 14) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 15) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 15) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 16) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name),i.indid, 16) + ']'
               END +
               ') WITH FILLFACTOR = 90'
             , 'DROP INDEX ['
             + u.name
             + '].['
             + o.name
             + '].['
             + i.name
             + ']'
          , 'CU_'
             + o.name
             + '_'
             + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1)
             + CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
                       ''
                    ELSE
                       '_Ect'
               END
          , i.name
          , 'CU'
     FROM sysobjects o WITH (NOLOCK) LEFT OUTER JOIN sysindexes i WITH (NOLOCK)
                                  ON o.id = i.id
                               INNER JOIN sysusers u WITH (NOLOCK)
                                  ON o.uid = u.uid
    WHERE (o.type = 'U')
      AND (i.indid = 1)
      AND (i.status & (2048)) = 0
      AND (i.status & (4096)) = 0
      AND (i.status & (2 | 4096)) > 0
      AND (o.name <> 'dtproperties')
      AND o.uid   = user_id(N'dbo')
      AND (LEFT(o.name, 4) <> 'Fog_')
      AND (LEFT(o.name, 5) <> 'Spot_')
      AND (LEFT(i.name, 8) <> '_WA_Sys_')
 ORDER BY o.name, i.indid
   
-- Now create the Drop and Create statements for the unique non-clustered constraint indexes.
   INSERT INTO @tbl
             ( CreateStatement
             , DropStatement
             , ConstraintName
             , OrigConstraint
             , TypeOf
             )
        SELECT 'ALTER TABLE ['
             + u.name
             + '].['
             + o.name
             + '] ADD CONSTRAINT [CX_'
             + o.name
             + '_'
             + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1)
             + CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
                       ''
                    ELSE
                       '_Ect'
               END
             + '] UNIQUE NONCLUSTERED ([' 
             + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1) + ']'
               +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 3) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 3) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 4) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name),i.indid, 4) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 5) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 5) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 6) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 6) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 7) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 7) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 8) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 8) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 9) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 9) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 10) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 10) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 11) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 11) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 12) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 12) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 13) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 13) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 14) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 14) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 15) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 15) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 16) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name),i.indid, 16) + ']'
               END +
               ') WITH FILLFACTOR = 90'
             , 'ALTER TABLE ['
             + u.name
             + '].['
             + o.name
             + '] DROP CONSTRAINT ['
             + i.name
             + ']'
          , 'CX_'
             + o.name
             + '_'
             + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1)
             + CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
                       ''
                    ELSE
                       '_Ect'
               END
          , i.name
          , 'CX'
     FROM sysobjects o WITH (NOLOCK) LEFT OUTER JOIN sysindexes i WITH (NOLOCK)
                                  ON o.id = i.id
                               INNER JOIN sysusers u WITH (NOLOCK)
                                  ON o.uid = u.uid
    WHERE (o.type = 'U')
      AND (i.indid BETWEEN 2 AND 255)
      AND (i.status & 2048) = 0
      AND (i.status & 4096) > 0
      AND (o.name <> 'dtproperties')
      AND o.uid   = user_id(N'dbo')
      AND (LEFT(o.name, 4) <> 'Fog_')
      AND (LEFT(o.name, 5) <> 'Spot_')
      AND (LEFT(i.name, 8) <> '_WA_Sys_')
 ORDER BY o.name, i.indid
   
-- Now create the Drop and Create statements for the unique clustered constraint indexes.
   INSERT INTO @tbl
             ( CreateStatement
             , DropStatement
             , ConstraintName
             , OrigConstraint
             , TypeOf
             )
        SELECT 'ALTER TABLE ['
             + u.name
             + '].['
             + o.name
             + '] ADD CONSTRAINT [CC_'
             + o.name
             + '_'
             + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1)
             + CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
                       ''
                    ELSE
                       '_Ect'
               END
             + '] UNIQUE CLUSTERED([' 
             + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1) + ']'
               +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 3) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 3) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 4) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name),i.indid, 4) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 5) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 5) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 6) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 6) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 7) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 7) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 8) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 8) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 9) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 9) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 10) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 10) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 11) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 11) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 12) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 12) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 13) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 13) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 14) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 14) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 15) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 15) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 16) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name),i.indid, 16) + ']'
               END +
               ') WITH FILLFACTOR = 90'
             , 'ALTER TABLE ['
             + u.name
             + '].['
             + o.name
             + '] DROP CONSTRAINT ['
             + i.name
             + ']'
          , 'CC_'
             + o.name
             + '_'
             + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1)
             + CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
                       ''
                    ELSE
                       '_Ect'
               END
          , i.name
          , 'CC'
     FROM sysobjects o WITH (NOLOCK) LEFT OUTER JOIN sysindexes i WITH (NOLOCK)
                                  ON o.id = i.id
                               INNER JOIN sysusers u WITH (NOLOCK)
                                  ON o.uid = u.uid
    WHERE (o.type = 'U')
      AND (i.status & (2 | 4096) > 0)
      AND ((i.status & 4096) > 0)
      AND ((i.status & 2048) = 0)
      AND (i.indid = 1)
      AND (o.name <> 'dtproperties')
      AND o.uid   = user_id(N'dbo')
      AND (LEFT(o.name, 4) <> 'Fog_')
      AND (LEFT(o.name, 5) <> 'Spot_')
      AND (LEFT(i.name, 8) <> '_WA_Sys_')
 ORDER BY o.name, i.indid
   
-- Now create the Drop and Create statements for the prime key non-clustered indexes.
   INSERT INTO @tbl
             ( CreateStatement
             , DropStatement
             , ConstraintName
             , OrigConstraint
             , TypeOf
             )
        SELECT 'ALTER TABLE ['
             + u.name
             + '].['
             + o.name
             + '] ADD CONSTRAINT [PK_'
             + o.name
             + '_'
             + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1)
             + CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
                       ''
                    ELSE
                       '_Ect'
               END
             + '] PRIMARY KEY NONCLUSTERED ([' 
             + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1) + ']'
               +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 3) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 3) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 4) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name),i.indid, 4) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 5) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 5) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 6) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 6) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 7) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 7) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 8) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 8) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 9) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 9) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 10) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 10) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 11) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 11) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 12) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 12) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 13) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 13) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 14) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 14) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 15) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 15) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 16) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name),i.indid, 16) + ']'
               END +
               ') WITH FILLFACTOR = 90'
             , 'ALTER TABLE ['
             + u.name
             + '].['
             + o.name
             + '] DROP CONSTRAINT ['
             + i.name
             + ']'
          , 'PK_'
             + o.name
             + '_'
             + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1)
             + CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
                       ''
                    ELSE
                       '_Ect'
               END
          , i.name
          , 'PK'
     FROM sysobjects o WITH (NOLOCK) LEFT OUTER JOIN sysindexes i WITH (NOLOCK)
                                  ON o.id = i.id
                               INNER JOIN sysusers u WITH (NOLOCK)
                                  ON o.uid = u.uid
    WHERE (o.type = 'U')
      AND (i.indid BETWEEN 2 AND 255)
      AND (i.status & 2048) > 0
      AND (o.name <> 'dtproperties')
      AND o.uid   = user_id(N'dbo')
      AND (LEFT(o.name, 4) <> 'Fog_')
      AND (LEFT(o.name, 5) <> 'Spot_')
      AND (LEFT(i.name, 8) <> '_WA_Sys_')
 ORDER BY o.name, i.indid
   
-- Now create the Drop and Create statements for the prime key clustered indexes.
   INSERT INTO @tbl
             ( CreateStatement
             , DropStatement
             , ConstraintName
             , OrigConstraint
             , TypeOf
             )
        SELECT 'ALTER TABLE ['
             + u.name
             + '].['
             + o.name
             + '] ADD CONSTRAINT [PC_'
             + o.name
             + '_'
             + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1)
             + CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
                       ''
                    ELSE
                       '_Ect'
               END
             + '] PRIMARY KEY CLUSTERED ([' 
             + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1) + ']'
               +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 3) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 3) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 4) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name),i.indid, 4) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 5) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 5) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 6) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 6) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 7) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 7) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 8) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 8) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 9) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 9) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 10) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 10) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 11) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 11) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 12) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 12) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 13) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 13) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 14) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 14) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 15) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 15) + ']'
               END +
               CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 16) IS NULL THEN
                       ''
                    ELSE
                       ', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name),i.indid, 16) + ']'
               END +
               ') WITH FILLFACTOR = 90'
             , 'ALTER TABLE ['
             + u.name
             + '].['
             + o.name
             + '] DROP CONSTRAINT ['
             + i.name
             + ']'
          , 'PC_'
             + o.name
             + '_'
             + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1)
             + CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
                       ''
                    ELSE
                       '_Ect'
               END
          , i.name
          , 'PC'
     FROM sysobjects o WITH (NOLOCK) LEFT OUTER JOIN sysindexes i WITH (NOLOCK)
                                  ON o.id = i.id
                               INNER JOIN sysusers u WITH (NOLOCK)
                                  ON o.uid = u.uid
    WHERE (o.type = 'U')
      AND (i.indid = 1)
      AND (i.status & 2048) > 0
      AND (o.name <> 'dtproperties')
      AND o.uid   = user_id(N'dbo')
      AND (LEFT(o.name, 4) <> 'Fog_')
      AND (LEFT(o.name, 5) <> 'Spot_')
      AND (LEFT(i.name, 8) <> '_WA_Sys_')
 ORDER BY o.name, i.indid
   
-- Remove the list of user table objects from memory.
   DELETE
     FROM @tbl_all
   
-- Start a transaction.
BEGIN TRANSACTION
   
-- First drop the Foreign Key codes.
   SELECT @min_id = MIN(CountID)
        , @cnt    = MAX(CountID)
     FROM @tbl
    WHERE TypeOf  = 'FK'
   
   WHILE (@min_id <= @cnt)
   BEGIN
      SELECT @valD = DropStatement
           , @fkey = ConstraintName
        FROM @tbl
       WHERE (CountID = @min_id)
      
      IF (@valD IS NOT NULL)
         EXEC(@valD)
      
      IF (@@ERROR <> 0)
         GOTO Error_Exit
      
      SET @valD = NULL
      SET @i    = NULL
      
      SET @min_id = @min_id + 1
   END -- of WHILE (@min_id <= @cnt)
   
-- Drop the default constraints.
   SELECT @min_id = MIN(CountID)
        , @cnt    = MAX(CountID)
     FROM @tbl
    WHERE TypeOf  = 'DF'
   
   WHILE (@min_id <= @cnt)
   BEGIN
      SELECT @valD = DropStatement
           , @fkey = ConstraintName
        FROM @tbl
       WHERE (CountID = @min_id)
      
      IF (@valD IS NOT NULL)
         EXEC(@valD)
      
      IF (@@ERROR <> 0)
         GOTO Error_Exit
      
      SET @valD = NULL
      SET @i    = NULL
      
      SET @min_id = @min_id + 1
   END -- of WHILE (@min_id <= @cnt)
   
-- Drop the non unique indexes.
   SELECT @min_id = MIN(CountID)
        , @cnt    = MAX(CountID)
     FROM @tbl
    WHERE TypeOf  = 'IX'
   
   WHILE (@min_id <= @cnt)
   BEGIN
      SELECT @valD = DropStatement
           , @fkey = ConstraintName
        FROM @tbl
       WHERE (CountID = @min_id)
      
      IF (@valD IS NOT NULL)
         EXEC(@valD)
      
      IF (@@ERROR <> 0)
         GOTO Error_Exit
      
      SET @valD = NULL
      SET @i    = NULL
      
      SET @min_id = @min_id + 1
   END -- of WHILE (@min_id <= @cnt)
   
-- Drop the unique indexes.
   SELECT @min_id = MIN(CountID)
        , @cnt    = MAX(CountID)
     FROM @tbl
    WHERE TypeOf  = 'IU'
   
   WHILE (@min_id <= @cnt)
   BEGIN
      SELECT @valD = DropStatement
           , @fkey = ConstraintName
        FROM @tbl
       WHERE (CountID = @min_id)
      
      IF (@valD IS NOT NULL)
         EXEC(@valD)
      
      IF (@@ERROR <> 0)
         GOTO Error_Exit
      
      SET @valD = NULL
      SET @i    = NULL
      
      SET @min_id = @min_id + 1
   END -- of WHILE (@min_id <= @cnt)
   
-- Drop the non-unique clustered indexes.
   SELECT @min_id = MIN(CountID)
        , @cnt    = MAX(CountID)
     FROM @tbl
    WHERE TypeOf  = 'CL'
   
   WHILE (@min_id <= @cnt)
   BEGIN
      SELECT @valD = DropStatement
           , @fkey = ConstraintName
        FROM @tbl
       WHERE (CountID = @min_id)
      
      IF (@valD IS NOT NULL)
         EXEC(@valD)
      
      IF (@@ERROR <> 0)
         GOTO Error_Exit
      
      SET @valD = NULL
      SET @i    = NULL
      
      SET @min_id = @min_id + 1
   END -- of WHILE (@min_id <= @cnt)
   
-- Drop the unique clustered indexes.
   SELECT @min_id = MIN(CountID)
        , @cnt    = MAX(CountID)
     FROM @tbl
    WHERE TypeOf  = 'CU'
   
   WHILE (@min_id <= @cnt)
   BEGIN
      SELECT @valD = DropStatement
           , @fkey = ConstraintName
        FROM @tbl
       WHERE (CountID = @min_id)
      
      IF (@valD IS NOT NULL)
         EXEC(@valD)
      
      IF (@@ERROR <> 0)
         GOTO Error_Exit
      
      SET @valD = NULL
      SET @i    = NULL
      
      SET @min_id = @min_id + 1
   END -- of WHILE (@min_id <= @cnt)
   
-- Drop the non-clustered constraint indexes.
   SELECT @min_id = MIN(CountID)
        , @cnt    = MAX(CountID)
     FROM @tbl
    WHERE TypeOf  = 'CX'
   
   WHILE (@min_id <= @cnt)
   BEGIN
      SELECT @valD = DropStatement
           , @fkey = ConstraintName
        FROM @tbl
       WHERE (CountID = @min_id)
      
      IF (@valD IS NOT NULL)
         EXEC(@valD)
      
      IF (@@ERROR <> 0)
         GOTO Error_Exit
      
      SET @valD = NULL
      SET @i    = NULL
      
      SET @min_id = @min_id + 1
   END -- of WHILE (@min_id <= @cnt)
   
-- Drop the clustered constraint indexes.
   SELECT @min_id = MIN(CountID)
        , @cnt    = MAX(CountID)
     FROM @tbl
    WHERE TypeOf  = 'CC'
   
   WHILE (@min_id <= @cnt)
   BEGIN
      SELECT @valD = DropStatement
           , @fkey = ConstraintName
        FROM @tbl
       WHERE (CountID = @min_id)
      
      IF (@valD IS NOT NULL)
         EXEC(@valD)
      
      IF (@@ERROR <> 0)
         GOTO Error_Exit
      
      SET @valD = NULL
      SET @i    = NULL
      
      SET @min_id = @min_id + 1
   END -- of WHILE (@min_id <= @cnt)
   
-- Drop the non-clustered Primary Key constraints.
   SELECT @min_id = MIN(CountID)
        , @cnt    = MAX(CountID)
     FROM @tbl
    WHERE TypeOf  = 'PK'
   
   WHILE (@min_id <= @cnt)
   BEGIN
      SELECT @valD = DropStatement
           , @fkey = ConstraintName
        FROM @tbl
       WHERE (CountID = @min_id)
      
      IF (@valD IS NOT NULL)
         EXEC(@valD)
      
      IF (@@ERROR <> 0)
         GOTO Error_Exit
      
      SET @valD = NULL
      SET @i    = NULL
      
      SET @min_id = @min_id + 1
   END -- of WHILE (@min_id <= @cnt)
   
-- Drop the clustered Primary Key constraints.
   SELECT @min_id = MIN(CountID)
        , @cnt    = MAX(CountID)
     FROM @tbl
    WHERE TypeOf  = 'PC'
   
   WHILE (@min_id <= @cnt)
   BEGIN
      SELECT @valD = DropStatement
           , @fkey = ConstraintName
        FROM @tbl
       WHERE (CountID = @min_id)
      
      IF (@valD IS NOT NULL)
         EXEC(@valD)
      
      IF (@@ERROR <> 0)
         GOTO Error_Exit
      
      SET @valD = NULL
      SET @i    = NULL
      
      SET @min_id = @min_id + 1
   END -- of WHILE (@min_id <= @cnt) */   
   IF (@drop_only = 0)
      BEGIN
-- Re-add all the indexex back to the database by transversing the
-- @tbl records in reverse order.
         SELECT @min_id = MIN(CountID)
              , @cnt    = MAX(CountID)
           FROM @tbl
         
         SET @i    = NULL
         SET @valI = NULL
         
         WHILE @cnt >= @min_id
         BEGIN
            SELECT @valI = CreateStatement
                 , @fkey = ConstraintName
              FROM @tbl
             WHERE (CountID = @cnt)
            
            SELECT @i = COUNT(CountID)
              FROM @tbl
             WHERE (ConstraintName = @fkey)
               AND (CountID > @cnt)
            
            IF (@i = 0)
               EXEC(@valI) --PRINT @valI
            ELSE
               PRINT 'Found Duplicate for: ' + @fkey
            
            IF (@@ERROR <> 0)
               BEGIN
                  PRINT 'An Error Occured...'
                  GOTO Error_Exit
               END
            
            SET @i    = 0
            SET @valI = NULL
            
            SET @cnt = @cnt - 1
         END -- of WHILE @cnt >= @min_id */      END -- of IF (@drop_only = 0)
      
   IF (@@ERROR = 0)
      COMMIT TRANSACTION
   ELSE
      BEGIN
Error_Exit:
         ROLLBACK TRANSACTION
      END --
   
RETURN

Rate

Share

Share

Rate