Generate CREATE Scripts for Index List

Question

As part of the mapping change exercise, I have a list of indexes (122) that need to be deleted and then created again. How can I recreate these indexes without having to go through the GUI and write it to the query window each time?

My index list is derived from this script

WITH indexCTE AS ( SELECT Table_Name, Column_Name, Collation_Name FROM information_schema.columns WHERE Collation_Name IS NOT NULL AND Collation_Name = 'Modern_Spanish_CI_AS' ), indexCTE2 AS ( SELECT i.Name [Index Name], OBJECT_NAME(i.object_ID) [Table Name], c.Name [Column Name] FROM sys.indexes i INNER JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id INNER JOIN sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.OBJECT_ID WHERE EXISTS (SELECT 1 FROM indexCTE t1 WHERE t1.Table_Name = OBJECT_NAME(i.object_ID) AND t1.Column_Name = c.Name) ) SELECT * FROM indexCTE2 

As you can probably tell, I'm still a junior database administrator, so please be patient with me!

Thank!

Answer 1

You're pretty close, I would say I tried this, can you check if this works for you and shows you the expected 122 indexes that need to be recreated?

UPDATE : added functionality for determining the index type CLUSTERED vs .NONCLUSTERED and adding INCLUDEd columns to the index definition.

 WITH indexCTE AS ( SELECT DISTINCT i.index_id, i.name, i.object_id FROM sys.indexes i INNER JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id WHERE EXISTS (SELECT * FROM sys.columns c WHERE c.collation_name = 'Modern_Spanish_CI_AS' AND c.column_id = ic.column_id AND c.object_id = ic.object_id) ), indexCTE2 AS ( SELECT indexCTE.name 'IndexName', OBJECT_NAME(indexCTE.object_ID) 'TableName', CASE indexCTE.index_id WHEN 1 THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END AS 'IndexType', (SELECT DISTINCT c.name + ',' FROM sys.columns c INNER JOIN sys.index_columns ic ON c.object_id = ic.object_id AND ic.column_id = c.column_id AND ic.Is_Included_Column = 0 WHERE indexCTE.OBJECT_ID = ic.object_id AND indexCTE.index_id = ic.index_id FOR XML PATH('') ) ixcols, ISNULL( (SELECT DISTINCT c.name + ',' FROM sys.columns c INNER JOIN sys.index_columns ic ON c.object_id = ic.object_id AND ic.column_id = c.column_id AND ic.Is_Included_Column = 1 WHERE indexCTE.OBJECT_ID = ic.object_id AND indexCTE.index_id = ic.index_id FOR XML PATH('') ), '') includedcols FROM indexCTE ) SELECT 'CREATE ' + IndexType + ' INDEX ' + IndexName + ' ON ' + TableName + '(' + SUBSTRING(ixcols, 1, LEN(ixcols)-1) + CASE LEN(includedcols) WHEN 0 THEN ')' ELSE ') INCLUDE (' + SUBSTRING(includedcols, 1, LEN(includedcols)-1) + ')' END FROM indexCTE2 ORDER BY TableName, IndexName 

Do you get the CREATE INDEX instructions you are looking for?

Mark

Answer 2

Great script mark. The only thing that, in my opinion, is missing is an indicator of an ascending or descending order in each column. I modified your script to include a case statement for indexed columns to add to ASC or DESC depending on the is_descending_key column of the sys.index_columns view.

 WITH indexCTE AS ( SELECT DISTINCT i.index_id, i.name, i.object_id FROM sys.indexes i INNER JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id WHERE EXISTS (SELECT * FROM sys.columns c WHERE c.collation_name = 'Modern_Spanish_CI_AS' AND c.column_id = ic.column_id AND c.object_id = ic.object_id) ), indexCTE2 AS ( SELECT indexCTE.name 'IndexName', OBJECT_NAME(indexCTE.object_ID) 'TableName', CASE indexCTE.index_id WHEN 1 THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END AS 'IndexType', (SELECT CASE WHEN ic.is_descending_key = 1 THEN c.name + ' DESC ,' ELSE c.name + ' ASC ,' END FROM sys.columns c INNER JOIN sys.index_columns ic ON c.object_id = ic.object_id AND ic.column_id = c.column_id AND ic.Is_Included_Column = 0 WHERE indexCTE.OBJECT_ID = ic.object_id AND indexCTE.index_id = ic.index_id FOR XML PATH('') ) ixcols, ISNULL( (SELECT DISTINCT c.name + ',' FROM sys.columns c INNER JOIN sys.index_columns ic ON c.object_id = ic.object_id AND ic.column_id = c.column_id AND ic.Is_Included_Column = 1 WHERE indexCTE.OBJECT_ID = ic.object_id AND indexCTE.index_id = ic.index_id FOR XML PATH('') ), '') includedcols FROM indexCTE ) SELECT 'CREATE ' + IndexType + ' INDEX ' + IndexName + ' ON ' + TableName + '(' + SUBSTRING(ixcols, 1, LEN(ixcols)-1) + CASE LEN(includedcols) WHEN 0 THEN ')' ELSE ') INCLUDE (' + SUBSTRING(includedcols, 1, LEN(includedcols)-1) + ')' END FROM indexCTE2 ORDER BY TableName, IndexName 

Answer 3

 DECLARE @T_IndexInfo TABLE ( IndID NVARCHAR(128), ObjectID NVARCHAR(128), ColID NVARCHAR(128), IndexName NVARCHAR(128), TableName NVARCHAR(128), ColumnName NVARCHAR(128), KeyNo NVARCHAR(128), ColType NVARCHAR(128) ) INSERT INTO @T_IndexInfo SELECT I.IndID, SO.ID AS 'ObjectID', SK.ColID, I.Name AS 'IndexName', SO.Name AS 'TableName', SC.Name AS 'ColumnName', Sk.KeyNo, CASE WHEN Sk.KeyNo = 0 THEN 'Include' ELSE 'Normal' END AS 'ColType' FROM sys.sysindexes I INNER JOIN sys.sysobjects SO ON SO.ID = I.ID AND SO.xtype = 'U' INNER JOIN sys.sysindexkeys SK ON SK.IndID = I.IndID AND SO.ID = SK.ID INNER JOIN sys.syscolumns SC ON SC.ID = SO.ID AND SC.ColID = SK.ColID WHERE I.IndID > 0 AND I.IndID < 255 AND ( I.Status & 64 ) = 0 -- AND ( I.status & 2048 ) <> 2048 /******** comment this if PK also need to be recreated *****/ ORDER BY SO.Name, I.Name DECLARE @T_Final TABLE ( TableName NVARCHAR(128), IndexName NVARCHAR(128), NormalColumns NVARCHAR(MAX), IncludedColumns NVARCHAR(MAX) ) INSERT INTO @T_Final SELECT DISTINCT TableName, IndexName, STUFF(( SELECT ',[' + ColumnName + ']' FROM @T_IndexInfo WHERE IndID = I.IndID AND ObjectID = I.ObjectID AND ColType = 'Normal' ORDER BY KeyNo FOR XML PATH('') ), 1, 1, '') AS 'NormalColumns', STUFF(( SELECT ',[' + ColumnName + ']' FROM @T_IndexInfo WHERE IndID = I.IndID AND ObjectID = I.ObjectID AND ColType = 'Include' FOR XML PATH('') ), 1, 1, '') AS 'IncludedColumns' FROM @T_IndexInfo I; WITH indexCTE AS ( SELECT Table_Name, Column_Name --, Collation_Name FROM information_schema.columns WHERE Collation_Name IS NOT NULL AND Collation_Name = 'Modern_Spanish_CI_AS' ), indexCTE2 AS ( SELECT i.Name [Index Name], OBJECT_NAME(i.object_ID) [Table Name], c.Name [Column Name] FROM sys.indexes i INNER JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id INNER JOIN sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.OBJECT_ID WHERE EXISTS (SELECT 1 FROM indexCTE t1 WHERE t1.Table_Name = OBJECT_NAME(i.object_ID) AND t1.Column_Name = c.Name) ) SELECT IndexName, TableName, NormalColumns, IncludedColumns INTO #temp1 FROM @T_Final z INNER JOIN indexCTE2 x ON z.IndexName = x.[Index Name] -- To generate CREATE INDEX SCRIPT SELECT 'CREATE INDEX [' + IndexName + '] ON [' + TableName + '].(' + NormalColumns + ')' + CASE WHEN IncludedColumns IS NULL THEN '' ELSE ' INCLUDE (' + IncludedColumns + ')' END AS 'CreateScript' FROM #temp1 -- To generate DROP INDEX SCRIPT SELECT 'DROP INDEX [' + TableName + '].[' + IndexName + ']' AS 'DropScript' FROM #temp1 

TABLE DROP # temp1

Answer 4

This is a little off topic, but I thought I was suggesting this anyway:

If you do not want to continue to run your scripts in the sql server management studio, you can create a runmyscripts.bat file containing something like:

 @echo off echo Execute Scripts... sqlcmd -i C:\Scripts\myscript1.sql sqlcmd -i C:\Scripts\myscript2.sql echo Scripts Complete. echo Press any button to exit. pause 

Answer 5

There is a relative complete solution in TechNet .

Adjust the request as you wish:

• sys.tables for sys.views
• remove default values ​​in element
• delete / add some conditions