Script:產生DB所有Table的INDEX

DECLARE @PKSQL VARCHAR(8000)

-- Get all existing indexes, but NOT the primary keys

DECLARE cIX CURSOR FOR

   SELECT OBJECT_NAME(SI.Object_ID), SI.Object_ID, SI.Name, SI.Index_ID,SF.NAME FileGroupName

      FROM SYS.INDEXES SI

         LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON SI.Name = TC.CONSTRAINT_NAME AND OBJECT_NAME(SI.Object_ID) = TC.TABLE_NAME

         LEFT JOIN SYS.FILEGROUPS SF ON SI.DATA_SPACE_ID = SF.DATA_SPACE_ID

         --取得非PK的INDEX

      WHERE SI.is_primary_key =0  AND OBJECTPROPERTY(SI.Object_ID, 'IsUserTable') = 1

      ORDER BY OBJECT_NAME(SI.Object_ID), SI.Index_ID

DECLARE @IxTable SYSNAME

DECLARE @IxTableID INT

DECLARE @IxName SYSNAME

DECLARE @IxID INT

DECLARE @FileGroupName SYSNAME

-- Loop through all indexes

OPEN cIX

FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID,@FileGroupName

WHILE (@@FETCH_STATUS = 0)

BEGIN

   DECLARE @IXSQL NVARCHAR(4000) SET @PKSQL = ''

   SET @IXSQL = 'CREATE '

   -- Check if the index is unique

   IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsUnique') = 1)

      SET @IXSQL = @IXSQL + 'UNIQUE '

   -- Check if the index is clustered

   IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)

      SET @IXSQL = @IXSQL + 'CLUSTERED '

   ELSE

      SET @IXSQL = @IXSQL + 'NONCLUSTERED '

  

   SET @IXSQL = @IXSQL + 'INDEX ' + @IxName + ' ON ' + @IxTable + '('

   -- Get all columns of the index

   --INDEX的欄位的排序(ASC或是DESC)與順序對資料庫的SELECT效能有很大的影響， 

   --使用SYS.INDEX_COLUMNS的IS_DESCENDING_KEY判斷INDEX的欄位是ASC或是DESC

   DECLARE cIxColumn CURSOR FOR

      SELECT SC.Name + CASE WHEN IS_DESCENDING_KEY=0 THEN ' ASC' ELSE ' DESC ' END

      FROM SYS.INDEX_COLUMNS IC

         JOIN SYS.COLUMNS SC ON IC.OBJECT_ID = SC.OBJECT_ID AND IC.Column_ID = SC.Column_ID

      WHERE IC.OBJECT_ID = @IxTableID AND Index_ID = @IxID

      --使用SYS.INDEX_COLUMNS.KEY_ORDINAL列出INDEX的欄位的順序

      ORDER BY IC.KEY_ORDINAL

   DECLARE @IxColumn SYSNAME

   DECLARE @IxFirstColumn BIT SET @IxFirstColumn = 1

   -- Loop throug all columns of the index and append them to the CREATE statement

   OPEN cIxColumn

   FETCH NEXT FROM cIxColumn INTO @IxColumn

   WHILE (@@FETCH_STATUS = 0)

   BEGIN

      IF (@IxFirstColumn = 1)

         SET @IxFirstColumn = 0

      ELSE

         SET @IXSQL = @IXSQL + ', '

      SET @IXSQL = @IXSQL + @IxColumn

      FETCH NEXT FROM cIxColumn INTO @IxColumn

   END

   CLOSE cIxColumn

   DEALLOCATE cIxColumn

   -- Append FileGroup

   SET @IXSQL = @IXSQL + ')'+' ON'+' '+'['+@FileGroupName+']'

   -- Print out the CREATE statement for the index

   PRINT @IXSQL

   FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID,@FileGroupName

END

CLOSE cIX

DEALLOCATE cIX

參考網址