SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Script existing indices (SQL Server 2k8)

 
Reply to topic    SoftTree Technologies Forum Index » Tips & Snippets Repository View previous topic
View next topic
Script existing indices (SQL Server 2k8)
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2100

Post Script existing indices (SQL Server 2k8) Reply with quote
I use this script to script indices for the selected table:

Code:

-- script existing indices on a table
$PROMPT(script_disabled,Script disabled indices?,,,Yes No)$
$PROMPT(drope,Drop existing indices?,,,1 0,Yes No)$
$PROMPT(usei,Include INCLUDE parts?,,,1 0,Yes No)$

$$DECLARE @targy VARCHAR(256), @elso_pont INT, @masodik_pont INT, @len INT, @sema VARCHAR(256), @tabla VARCHAR(256), @mezo VARCHAR(256)
SELECT @targy = '$OBJECT$', @elso_pont = CHARINDEX('.',@targy), @masodik_pont = CHARINDEX('.',@targy,CHARINDEX('.',@targy)+1), @len = LEN(@targy)
IF @masodik_pont = 0 SELECT @sema = PARSENAME(@targy,2), @tabla = PARSENAME(@targy,1) ELSE SELECT @sema = PARSENAME(@targy,3), @tabla = PARSENAME(@targy,2), @mezo = PARSENAME(@targy,1)

DECLARE   @sql NVARCHAR( MAX )
SET @sql = ''
SELECT
   @sql = @sql +
   + CASE
        WHEN ic.index_column_id = 1 AND i.is_primary_key = 1 AND $drope$ = 1
             AND RANK() OVER( PARTITION BY i.index_id ORDER BY ic.index_column_id ASC ) = 1 THEN ''
             + CASE
                  WHEN i.is_disabled = 1 THEN '-- WARNING !!! INDEX DISABLED !!! WARNING --' -- + CHAR( 10 )
                  ELSE '-- LIVE INDEX! --'
               END
             + CHAR( 10 )
             + 'IF  EXISTS (SELECT * FROM sys.indexes WHERE '
             + 'object_id = OBJECT_ID(N''' + s.name + '.' + t.name + ''') AND name = N''' + i.name + ''')' + CHAR( 10 )
             + 'ALTER TABLE ' + s.name + '.' + t.name +
             ' DROP CONSTRAINT ' + i.name + CHAR( 10 ) + 'GO' + CHAR( 10 )
             + CHAR( 10 )
        WHEN ic.index_column_id = 1 AND i.is_primary_key = 0 AND $drope$ = 1
             AND RANK() OVER( PARTITION BY i.index_id ORDER BY ic.index_column_id ASC ) = 1 THEN ''
             + CASE
                  WHEN i.is_disabled = 1 THEN '-- WARNING !!! INDEX DISABLED !!! WARNING --' + CHAR( 10 )
                  ELSE '-- LIVE INDEX! --'
               END
             + CHAR( 10 )
             + 'IF  EXISTS (SELECT * FROM sys.indexes WHERE '
             + 'object_id = OBJECT_ID(N''' + s.name + '.' + t.name + ''') AND name = N''' + i.name + ''')' + CHAR( 10 )
             + 'DROP INDEX ' + i.name + ' ON ' + s.name + '.' + t.name
             + CHAR( 10 ) + 'GO' + + CHAR( 10 ) + CHAR( 10 )
        ELSE ''
     END
   + CASE
        WHEN ic.index_column_id = 1 AND i.is_primary_key = 1
             AND RANK() OVER( PARTITION BY i.index_id ORDER BY ic.index_column_id ASC ) = 1 THEN 'ALTER TABLE '
             + s.name + '.' + t.name + ' ADD CONSTRAINT ' + i.name + ' PRIMARY KEY ' + CASE i.[type]
                                                                                          WHEN 1 THEN 'CLUSTERED'
                                                                                          WHEN 2 THEN 'NONCLUSTERED'
                                                                                          ELSE '!!!error 2!!!'
                                                                                       END
        WHEN ic.index_column_id = 1 AND i.is_primary_key = 0
             AND RANK() OVER( PARTITION BY i.index_id ORDER BY ic.index_column_id ASC ) = 1 THEN 'CREATE '
             + CASE i.is_unique
                  WHEN 0 THEN ''
                  WHEN 1 THEN 'UNIQUE '
                  ELSE '!!!error UNIQUE!!!'
               END
             + CASE i.type
                  WHEN 1 THEN 'CLUSTERED'
                  WHEN 2 THEN 'NONCLUSTERED'
                  ELSE '!!!error CLUSTER!!!'
               END
             + ' INDEX ' + i.name + ' ON ' + t.name
        ELSE ''
     END
   + CASE
        WHEN RANK() OVER( PARTITION BY i.index_id ORDER BY ic.index_column_id ASC ) = 1 THEN CHAR( 10 ) + '('
        WHEN ic.is_included_column = 1 AND $usei$ = 1
             AND RANK() OVER( PARTITION BY i.index_id ,ic.is_included_column ORDER BY ic.index_column_id ) = 1 THEN CHAR( 10 )
             + ')' + CHAR( 10 ) + 'INCLUDE' + CHAR( 10 ) + '('
        ELSE ''
     END
   + CASE
        WHEN ( RANK() OVER( PARTITION BY i.index_id ORDER BY ic.index_column_id ASC ) = 1 )
             OR ( RANK() OVER( PARTITION BY i.index_id ,ic.is_included_column ORDER BY ic.index_column_id ASC ) = 1 ) THEN ''
             + CASE
                  WHEN ic.is_included_column = 0 OR $usei$ = 1 THEN CHAR( 10 ) + '   ' + c.name
                  ELSE ''
               END
        ELSE CASE
                WHEN ic.is_included_column = 0 OR $usei$ = 1 THEN CHAR( 10 ) + '   ,' + c.name
                ELSE ''
             END
     END
   + CASE
        WHEN ic.is_descending_key = 1 AND ic.is_included_column = 0 THEN ' DESC'
        WHEN ic.is_descending_key = 0 AND ic.is_included_column = 0 THEN ' ASC'
        ELSE ''
     END
   + CASE
        WHEN RANK() OVER( PARTITION BY i.index_id ORDER BY ic.index_column_id DESC ) = 1 THEN CHAR( 10 ) + ')' + CHAR( 10 ) + 'GO' + CHAR( 10 ) + CHAR( 10 )
             + CHAR( 10 )
        ELSE '' --ELSE CHAR( 10 ) + ')' + CHAR( 10 ) + 'GO' + CHAR( 10 ) --'!!!error 6!!!'
     END
FROM
   sys.schemas s
   INNER JOIN
   sys.tables t
      ON   t.schema_id = s.schema_id
   INNER JOIN
   sys.indexes i
      ON   i.object_id = t.object_id
   INNER JOIN
   sys.index_columns ic
      ON   ic.object_id = t.object_id
      AND   ic.index_id = i.index_id
   INNER JOIN
   sys.columns c
      ON   c.object_id = t.object_id
      AND   ic.column_id = c.column_id
WHERE   i.index_id > 0
   AND   i.type IN ( 1 ,2 ) -- clustered & nonclustered only
   AND   i.is_hypothetical = 0
   AND ( i.is_disabled = 0 OR '$script_disabled$' = 'Yes')
   AND   s.name = @sema
   AND   t.name = @tabla
ORDER BY
   i.index_id
   ,ic.index_column_id
SELECT @sql
$$

Thu Jun 30, 2011 10:34 am View user's profile Send private message
robertcorey



Joined: 12 Jan 2018
Posts: 1
Country: United States

Post Reply with quote
Thanks for sharing excellent information.Interesting it is a script submission.Sometimes as a DBA need to generate a T-SQL script for dropping and creating indexes in our SQL Server databases. T-SQL Script to Create All SQL Server Indexes

Here are some things to be aware of when generating the creation script:

If the index was disabled, then the code to disable it will also be generated.
If the index has included columns then they will be included in the same order.
The creation script considers all properties of the index (FILEGROUP, ALLOW_PAGE_LOCKS, STATISTICS_NORECOMPUTE, FILLFACTOR, SORT_IN_TEMPDB, etc.). You will not lose any index properties, the final script will create the original index.
Will not script indexes linked to the primary key
Will not script partitioned indexes

Thank you! This has really helped me to capture all the indexes.
Fri Jan 12, 2018 4:35 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2100

Post Reply with quote
This one I created when we were using SQL Server 2008. As you can see it's pretty aged now and it's far from being perfect. You're welcome to adapt it to your needs, refine or upgrade it in any way you see fit or necessary, and if done so, I'd be glad if you uploaded the upgraded version here so that we all can learn and benefit from it ;)
Sat Jan 13, 2018 5:32 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » Tips & Snippets Repository All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.