Scripting off indexes.

  • Looking for T-SQL that scripts off all indexes for a table in a SQL Server 2000 database (Windows 2000). Scenario: Need T-SQL to drop all indexes on a table and rebuild them after a BCP. Therefore, I am looking to see if someone has already coded this.

  • You can check the script library, but offhand, a quick cursor through sysindexes should get you the DROPs and creates. Or use the script generation facility in EM to script the table and it's indexes, that's probably quicker.

  • Thanks for the information but I am looking for something deeper. I failed to mention that I want this to work in a batch mode. Therefore, EM will not work. Yes, I did consider writing my own but I thought if someone has already created a similar script, I could leaverage off of it. I am trying to make my script smart enough so that if another DBA adds or removes indexes, my script will cover dropping all indexes and rebuilding them without having to worry about what someone did during the day.

    I found a stored procedure another person created called sp_NCHelpIndex which provides index information for tables. I thought about using the information from this stored procedure to build my DROP INDEX and CREATE INDEX statements.

  • DarylAndDaryl,

    Sent you a private message regarding scripts.

    Steve

  • This should do what you want:

    http://www.sqlservercentral.com/scripts/contributions/1035.asp

    DO NOT ACCIDENTALLY RUN THIS SCRIPT IN A PRODUCTION DATABASE unless you know what you are doing. It will drop all your FK relationships.

    Run it in Text Mode in Query analyzer.

    The output is in 2 forms:

    (1) The actual results in QA which is itself a script that will let you reconstruct all the FKs.

    (2) a table, one column of which also contains the SQL you can use to reconstruct your FKs.

    Don't be afraid of it. It really is good and works well. Just try it out a few times in a test database first an become familiar with how it works.

    G. Milner

  • IMO it's tricky to drop all indexes just to do a BCP.

    This will hurt you system _and_ it will hurt again when you recreate these indexes !

    And in the mean time, your applications (and other connections ? ) will have the opportunity to put in some messy data because of the lack of constraints that rely on indexes (pk - unique).

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • I'm with you here. Isn't this one of the reasons why the idea of a staging environment was conceived?

    BCP into staging tables that are sans indexes. Then insert/update/delete production tables as necessary.

     

     

    --------------------
    Colt 45 - the original point and click interface

  • Thanks for all the info.

    I need to add that this data is being BCPed to staging tables. These are the tables I want to drop the indexes on and recreate them after the BCP completes. From experience with reloading the data, we gained significate reload time by dropping the indexes and rebuilding them afterwards.

  • maybe sp_helpindex 'yourobject' as a base for your generate script may be quiet usable combined with the undocumend sp_meforeachtb.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Try this

     

    /****** Object:  Stored Procedure dbo.index_all_tables    Script Date: 5/19/00 12:24:02 PM ******/

    CREATE PROCEDURE index_all_tables

    AS

    /*

     This procedure will generate a short report of the sp_helpindex

     output for all tables within this database.

    */

    DECLARE @tablename varchar(30)

    DECLARE @tablename_header varchar(75)

    DECLARE tnames_cursor CURSOR FOR SELECT name

     FROM sysobjects

       WHERE type = 'U' ORDER BY name

    OPEN tnames_cursor

     FETCH NEXT FROM tnames_cursor INTO @tablename

      WHILE (@@FETCH_STATUS <> -1)

       BEGIN

     /*

      Because @@FETCH_STATUS will return one of three values, -2,

      -1, or 0, all three cases must be tested. In this case, if

      a table has been dropped since the time this stored

      procedure was executed, it will be skipped. A

      successful fetch (0) will cause the sp_helpindex within the

      BEGIN..END loop to execute.

     */

       IF (@@FETCH_STATUS <> -2)

        BEGIN

        PRINT " "

        SELECT @tablename_header = "**************  " +

          RTRIM(@tablename) + "  *************"

        PRINT @tablename_header

        PRINT " "

        EXEC sp_helpindex  @tablename

        END

        FETCH NEXT FROM tnames_cursor INTO @tablename

       END

     PRINT " "

     PRINT " "

     SELECT @tablename_header = "*************  NO MORE TABLES"

       + "  *************"

     PRINT @tablename_header

     PRINT " "

     PRINT "sp_helpindex has been run against all user-defined tables."

    DEALLOCATE tnames_cursor

    GO

  • Here's some thing that should work for you.. I've not tested this extensively for all types of constraints, but it works with basic Primary Keys, Foreign Keys and Standard Indexes.   I think it might be better if all the Foreign keys were dropped separately versus because of their Primary key dependancy.    You should obviously script all your indexes and foreign keys first so you can put them back.

    CREATE PROCEDURE usp_DropAllIndexesForaTable

       @TableName varchar(255)

    AS

    DECLARE @IndexName varchar(500)

    DECLARE @strSQL varchar(8000)

    DECLARE @fkeyName varchar(500)

    DECLARE @fKeyTableName varchar(255)

    CREATE TABLE #tmpIndexes

       (Index_Name sysname

       ,index_Description varchar(500)

       ,index_keys varchar(1000))

    CREATE TABLE #tmpPrimaryKey

       (Table_Qualifier varchar(255)

       ,Table_Owner varchar(255)

       ,Table_Name varchar(255)

       ,Column_Name varchar(255)

       ,Key_Seq int

       ,pk_Name varchar(500))

    CREATE TABLE #tmpFKeys

       (PKTable_Qualifier varchar(255)

       ,pktable_owner varchar(255)

       ,pkTable_Name varchar(255)

       ,pkColumn_Name varchar(255)

       ,fkTable_Qualifier varchar(255)

       ,fkTable_owner varchar(255)

       ,fkTable_Name varchar(255)

       ,fkColumn_Name varchar(255)

       ,Key_Seq int

       ,Update_Rule int

       ,Delete_Rule int

       ,fk_Name varchar(500)

       ,PK_Name varchar(500)

       ,Deferrability int

       )

     

    --// Get the Primary Keys, Foreign Keys and Indexes.

    EXEC ('INSERT INTO #tmpPrimaryKey EXEC sp_pKeys ' + @tableName)  

    EXEC ('INSERT INTO #tmpFKeys EXEC sp_fKeys ' + @tableName)  

    EXEC ('Insert into #tmpIndexes exec sp_helpIndex ' + @TableName)

    --//Loop through all the declare indexes

    DECLARE idx CURSOR FOR

    SELECT Index_Name from #tmpIndexes

    OPEN idx

    FETCH NEXT FROM idx into @IndexName

    WHILE (@@Fetch_Status <> -1)

    BEGIN

       SELECT @strSQL = ''

      

      

       IF EXISTS (select 1 from #tmpPrimaryKey where pk_Name = @IndexName)

       BEGIN

         

          --// Before you can drop a primary key, you must drop all the Foreign Keys that are constraining it

          --// Drop the foreign Keys that use the primary Key if any

          DECLARE fkey CURSOR FOR

          SELECT DISTINCT fkTable_Name,fk_Name

          FROM #tmpfKeys where pk_Name = @IndexName

          OPEN fKey

          FETCH NEXT FROM fkey into @fKeyTableName,@fkeyName

          WHILE (@@Fetch_Status <> -1)

          BEGIN

             EXEC ('Alter Table ' + @fKeyTableName + ' Drop Constraint ' + @fkeyName)

          FETCH NEXT FROM fkey into @fKeyTableName,@fkeyName

          END

          close fkey

          deallocate fkey

          --// All the fKey constraints should be gone, so drop the Primary key

          EXEC ('Alter Table ' + @TableName + ' Drop Constraint ' +  @IndexName)

       END ELSE BEGIN

       

      

          --// Drop all the other indexes not bound by constraints

          SELECT @strSQL = 'Drop Index ' + @TableName + '.' + @IndexName

          EXEC (@strSQL)

       END

    FETCH NEXT FROM idx into @IndexName

    END

    Close idx

    Deallocate idx

    hth

    Bill

Viewing 11 posts - 1 through 10 (of 10 total)

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