Scripting out all existing indexes in a SalesLogix database as “Create Index” statements

 I was recently asked to provide the ability to extract out of a SalesLogix SQL database, all of the indexes on every table.  There is no way of doing this through the SQL management studio but you can use a T-SQL script to do so.  Here is a sample script that will loop through all indexes on tables that do not start with “SYS” and builds a CREATE INDEX statement for each of them.

SELECT 
  REPLICATE(‘ ‘,4000) AS COLNAMES ,
  OBJECT_NAME(I.ID) AS TABLENAME,
  I.ID AS TABLEID,
  I.INDID AS INDEXID,
  I.NAME AS INDEXNAME,
  I.STATUS,
  INDEXPROPERTY (I.ID,I.NAME,’ISUNIQUE’) AS ISUNIQUE,
  INDEXPROPERTY (I.ID,I.NAME,’ISCLUSTERED’) AS ISCLUSTERED,
  INDEXPROPERTY (I.ID,I.NAME,’INDEXFILLFACTOR’

) AS INDEXFILLFACTOR
  INTO #TMP
  FROM SYSINDEXES I
  WHERE I.INDID > 0
  AND I.INDID < 255
  AND (I.STATUS & 64)=0
–uncomment below to eliminate PK or UNIQUE indexes;
–what i call ‘normal’ indexes
  –AND   INDEXPROPERTY (I.ID,I.NAME,’ISUNIQUE’)       =0
  –AND   INDEXPROPERTY (I.ID,I.NAME,’ISCLUSTERED’) =0

DECLARE
  @ISQL VARCHAR(4000),
  @TABLEID INT,
  @INDEXID INT,
  @MAXTABLELENGTH INT,
  @MAXINDEXLENGTH INT
  –USED FOR FORMATTING ONLY
    SELECT @MAXTABLELENGTH=MAX(LEN(TABLENAME)) FROM #TMP
    SELECT @MAXINDEXLENGTH=MAX(LEN(INDEXNAME)) FROM #TMP

    DECLARE C1 CURSOR FOR
      SELECT TABLEID,INDEXID FROM #TMP 
    OPEN C1
      FETCH NEXT FROM C1 INTO @TABLEID,@INDEXID
        WHILE @@FETCH_STATUS <> -1
          BEGIN
    SET @ISQL = ”
    SELECT @ISQL=@ISQL + ISNULL(SYSCOLUMNS.NAME,”) + ‘,’ FROM SYSINDEXES I
    INNER JOIN SYSINDEXKEYS ON I.ID=SYSINDEXKEYS.ID AND I.INDID=SYSINDEXKEYS.INDID
    INNER JOIN SYSCOLUMNS ON SYSINDEXKEYS.ID=SYSCOLUMNS.ID AND SYSINDEXKEYS.COLID=SYSCOLUMNS.COLID
    WHERE I.INDID > 0
    AND I.INDID < 255
    AND (I.STATUS & 64)=0
    AND I.ID=@TABLEID AND I.INDID=@INDEXID
    ORDER BY SYSCOLUMNS.COLID
    UPDATE #TMP SET COLNAMES=@ISQL WHERE TABLEID=@TABLEID AND INDEXID=@INDEXID

    FETCH NEXT FROM C1 INTO @TABLEID,@INDEXID
         END
      CLOSE C1
      DEALLOCATE C1
  –AT THIS POINT, THE ‘COLNAMES’ COLUMN HAS A TRAILING COMMA
  UPDATE #TMP SET COLNAMES=LEFT(COLNAMES,LEN(COLNAMES) -1)

  SELECT  ‘CREATE ‘
    + CASE WHEN ISUNIQUE     = 1 THEN ‘ UNIQUE ‘ ELSE ‘        ‘ END
    + CASE WHEN ISCLUSTERED = 1 THEN ‘ CLUSTERED ‘ ELSE ‘           ‘ END
    + ‘ INDEX [‘ + UPPER(INDEXNAME) + ‘]’
    + SPACE(@MAXINDEXLENGTH – LEN(INDEXNAME))
    +’ ON [‘ + UPPER(TABLENAME) + ‘] ‘
    + SPACE(@MAXTABLELENGTH – LEN(TABLENAME))
    + ‘(‘ + UPPER(COLNAMES) + ‘)’
    + CASE WHEN INDEXFILLFACTOR = 0 THEN ”  ELSE  ‘ WITH FILLFACTOR = ‘ + CONVERT(VARCHAR(10),INDEXFILLFACTOR)   END –AS SQL
    FROM #TMP where tablename not like’SYS%’

   –SELECT * FROM #TMP
   DROP TABLE #TMP