back indexes of a database

  • can I write a sql script or stored procedure that backsup all the indexes on a give database so that can be re-applied if required?

    I treid but I am not sure about how to find the columns involved in a give index and type pf index. I could just get index name from sysindexes table. But I cannot recreate the same index in some other database by just knowing its name right. please help he with sample script that would do so.

    Backup of indexes in any format is OK preferrable table format.

     

  • In 2005 Management Studio you can right click the database, Tasks -> Generate Scripts.... Follow the instructions and in the "what to script" section set everything to false except Indexes.

  • this free tool will script out all objects in any 2000 or 2005 database, using SMO. source is available so you can modify it as you please - it would be easy to change it so it only scripts a particular type of object, such as indexes.

    it's very useful for getting all your objects under source control.  also there's a .bat file that executes all the scripts to build a fresh db - I use it as a sort of autobuilder in conjunction with an app that monitors my source control.  it's a way to catch build breaks early.

    http://www.elsasoft.org/tools.htm

    ---------------------------------------
    elsasoft.org

  • i wrote this a while back:

    it scritps out all PK, Uq and regular indexes. remove the top 100 to get all results, this can be a HUGE resultset, depending on yout schema:

    Results:

    CREATE  UNIQUE  CLUSTERED  INDEX [SYSOBJECTS]                     ON [SYSOBJECTS]              (ID)

    CREATE  UNIQUE             INDEX [NCSYSOBJECTS]                       ON [SYSOBJECTS]              (NAME,UID)

    CREATE                         INDEX [NCSYSOBJECTS2]                      ON [SYSOBJECTS]              (PARENT_OBJ)

    SELECT TOP 100

    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

    --SELECT * FROM #TMP

    DROP TABLE #TMP

     

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • This is an ok script for some purposes perhaps, but it's missing a lot.  There are many options in CREATE INDEX that you are not covering, as you can see here: http://msdn2.microsoft.com/en-us/library/ms188783.aspx

    That's why I think it's safer to use SMO to script them - hundreds of hours of work went into getting the scripting capabilities of SMO correct - why not leverage them?

    ---------------------------------------
    elsasoft.org

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply