![SoftTree Technologies](templates/GreenTech/images/logo.gif) |
SoftTree Technologies
Technical Support Forums
|
|
Author |
Message |
gemisigo
Joined: 11 Mar 2010 Posts: 2109
|
|
Script existing indices (SQL Server 2k8) |
|
I use this script to script indices for the selected table:
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
-- 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 |
|
![](templates/GreenTech/images/spacer.gif) |
robertcorey
Joined: 12 Jan 2018 Posts: 1 Country: United States |
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
gemisigo
Joined: 11 Mar 2010 Posts: 2109
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
|
|
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
|
|
|