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: 1395

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
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.