How to script all indexes from a table (or an entire database)

SELECT ' CREATE ' +  
    CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END  +   
    I.type_desc COLLATE DATABASE_DEFAULT +' INDEX [' +    
    I.name  + '] ON '  +   
    Schema_name(T.Schema_id)+'.'+T.name + ' ( ' +  
    KeyColumns + ' )  ' +  
    ISNULL(' INCLUDE ('+IncludedColumns+' ) ','') +  
    ISNULL(' WHERE  '+I.Filter_definition,'') + ' WITH ( ' +  
    CASE WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON ' ELSE ' PAD_INDEX = OFF ' END + ','  +  
    'FILLFACTOR = '+CONVERT(CHAR(5),CASE WHEN I.Fill_factor = 0 THEN 100 ELSE I.Fill_factor END) + ','  +  
    -- default value  
    'SORT_IN_TEMPDB = OFF '  + ','  +  
    CASE WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON ' ELSE ' IGNORE_DUP_KEY = OFF ' END + ','  +  
    CASE WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF ' ELSE ' STATISTICS_NORECOMPUTE = ON ' END + ','  +  
    -- default value   
    --' DROP_EXISTING = ON '  + ','  +  
    -- default value   
    ' ONLINE = OFF '  + ','  +  
   CASE WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON ' ELSE ' ALLOW_ROW_LOCKS = OFF ' END + ','  +  
   CASE WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON ' ELSE ' ALLOW_PAGE_LOCKS = OFF ' END  + ' ) ON [' +  
   DS.name + ' ] '  [CreateIndexScript]  
FROM sys.indexes I    
 JOIN sys.tables T ON T.Object_id = I.Object_id     
 JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid    
 JOIN (SELECT * FROM (   
    SELECT IC2.object_id , IC2.index_id ,   
        STUFF((SELECT ' , ' + C.name + CASE WHEN MAX(CONVERT(INT,IC1.is_descending_key)) = 1 THEN ' DESC ' ELSE ' ASC ' END 
    FROM sys.index_columns IC1   
    JOIN Sys.columns C    
       ON C.object_id = IC1.object_id    
       AND C.column_id = IC1.column_id    
       AND IC1.is_included_column = 0   
    WHERE IC1.object_id = IC2.object_id    
       AND IC1.index_id = IC2.index_id    
    GROUP BY IC1.object_id,C.name,index_id   
    ORDER BY MAX(IC1.key_ordinal)   
       FOR XML PATH('')), 1, 2, '') KeyColumns    
    FROM sys.index_columns IC2    
    --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables   
    GROUP BY IC2.object_id ,IC2.index_id) tmp3 )tmp4    
  ON I.object_id = tmp4.object_id AND I.Index_id = tmp4.index_id   
 JOIN sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id    
 JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id    
 JOIN sys.filegroups FG ON I.data_space_id=FG.data_space_id    
 LEFT JOIN (SELECT * FROM (    
    SELECT IC2.object_id , IC2.index_id ,    
        STUFF((SELECT ' , [' + C.name  + ']'
    FROM sys.index_columns IC1    
    JOIN Sys.columns C     
       ON C.object_id = IC1.object_id     
       AND C.column_id = IC1.column_id     
       AND IC1.is_included_column = 1    
    WHERE IC1.object_id = IC2.object_id     
       AND IC1.index_id = IC2.index_id     
    GROUP BY IC1.object_id,C.name,index_id    
       FOR XML PATH('')), 1, 2, '') IncludedColumns     
   FROM sys.index_columns IC2     
   WHERE IC2.Object_id = object_id('tblERPInvoiceData') --Comment for all tables    
   GROUP BY IC2.object_id ,IC2.index_id) tmp1    
   WHERE IncludedColumns IS NOT NULL ) tmp2     
ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id    
WHERE I.is_primary_key = 0 AND I.is_unique_constraint = 0  
AND I.Object_id = object_id('tblERPInvoiceData') --Comment for all tables  
--AND I.name = 'IX_Address_PostalCode' --comment for all indexes

Result looks like this:
CREATE NONCLUSTERED INDEX [IX_tblERPInvoiceData_All] ON dbo.tblERPInvoiceData ( ClientID ASC , SupplierNumber ASC ) INCLUDE ( [Amount] , [CompanyID] , [Currency] , [DocumentDate] , [DocumentNumber] , [ID] , [InvoiceCredit] , [Matched] , [Number] , [PaidDate] , [Status] , [SystemID] , [Year] ) WITH ( PAD_INDEX = OFF ,FILLFACTOR = 90 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ]
CREATE NONCLUSTERED INDEX [IX_tblERPInvoiceData_ClientCompanySupplier] ON dbo.tblERPInvoiceData ( ClientID ASC , SystemID ASC , CompanyID ASC , SupplierNumber ASC ) INCLUDE ( [Amount] , [Currency] , [DocumentDate] , [DocumentNumber] , [ID] , [InvoiceCredit] , [Matched] , [Notes] , [Number] , [PaidDate] , [Status] , [Year] ) WITH ( PAD_INDEX = OFF ,FILLFACTOR = 90 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ]
CREATE NONCLUSTERED INDEX [IX_tblerpinvoicedata_data] ON dbo.tblERPInvoiceData ( DocumentDate ASC , DocumentNumber ASC , Amount ASC , InvoiceCredit ASC ) INCLUDE ( [ID] ) WITH ( PAD_INDEX = OFF ,FILLFACTOR = 90 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ]
CREATE NONCLUSTERED INDEX [IX_tblERPInvoiceData_DocDate] ON dbo.tblERPInvoiceData ( DtDocDate ASC ) INCLUDE ( [Amount] , [CompanyID] , [Currency] , [InvoiceCredit] , [PaidDate] , [SupplierNumber] , [SystemID] ) WITH ( PAD_INDEX = OFF ,FILLFACTOR = 90 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ]
CREATE NONCLUSTERED INDEX [IX_tblERPInvoiceData_DocNo_Status] ON dbo.tblERPInvoiceData ( DocumentNumber ASC ) INCLUDE ( [Amount] , [DocumentDate] , [InvoiceCredit] , [Status] ) WITH ( PAD_INDEX = OFF ,FILLFACTOR = 90 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ]
CREATE NONCLUSTERED INDEX [IX_tblERPInvoiceData_DTDate] ON dbo.tblERPInvoiceData ( DtDocDate ASC ) INCLUDE ( [CompanyID] , [ID] , [SupplierNumber] , [SystemID] ) WITH ( PAD_INDEX = OFF ,FILLFACTOR = 90 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ]
CREATE NONCLUSTERED INDEX [IX_tblERPInvoiceDAta_FileID] ON dbo.tblERPInvoiceData ( FileID ASC ) WITH ( PAD_INDEX = OFF ,FILLFACTOR = 90 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ]
CREATE NONCLUSTERED INDEX [IX_tblERPInvoiceData_IDc2] ON dbo.tblERPInvoiceData ( ClientID ASC , SystemID ASC , CompanyID ASC ) INCLUDE ( [Number] ) WITH ( PAD_INDEX = OFF ,FILLFACTOR = 90 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ]
CREATE NONCLUSTERED INDEX [IX_tblERPInvoiceData_MainIndex] ON dbo.tblERPInvoiceData ( ClientID ASC , Number ASC , SystemID ASC , CompanyID ASC ) INCLUDE ( [AddedBy] , [Amount] , [Currency] , [DateAdded] , [DocumentDate] , [DocumentNumber] , [DtDocDate] , [FileID] , [ID] , [InvoiceCredit] , [Matched] , [Notes] , [PaidDate] , [Status] , [SupplierNumber] , [Year] ) WITH ( PAD_INDEX = OFF ,FILLFACTOR = 90 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ]
CREATE NONCLUSTERED INDEX [IX_TblerpInvoiceData_Number_System_Company] ON dbo.tblERPInvoiceData ( Number ASC , SystemID ASC , CompanyID ASC ) WITH ( PAD_INDEX = OFF ,FILLFACTOR = 90 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ]
CREATE NONCLUSTERED INDEX [IX_tblERPInvoiceData_Statys] ON dbo.tblERPInvoiceData ( Status ASC ) INCLUDE ( [Amount] , [DocumentDate] , [DocumentNumber] , [InvoiceCredit] , [SupplierNumber] ) WITH ( PAD_INDEX = OFF ,FILLFACTOR = 90 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ]
CREATE NONCLUSTERED INDEX [IX_tblerpinvoicedata_supnr_sysm_client_comp] ON dbo.tblERPInvoiceData ( ClientID ASC , SystemID ASC , CompanyID ASC , SupplierNumber ASC ) INCLUDE ( [DocumentDate] , [ID] , [PaidDate] ) WITH ( PAD_INDEX = OFF ,FILLFACTOR = 90 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ]
CREATE NONCLUSTERED INDEX [IX_TblERPInvoiceDAta_System_Company] ON dbo.tblERPInvoiceData ( ClientID ASC , SystemID ASC , CompanyID ASC ) INCLUDE ( [Amount] , [Currency] , [DocumentDate] , [DocumentNumber] , [ID] , [InvoiceCredit] , [Number] , [SupplierNumber] ) WITH ( PAD_INDEX = OFF ,FILLFACTOR = 90 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ]
CREATE NONCLUSTERED INDEX [IX_tblERPInvoiceDAta_SyStemCompanyNUmber] ON dbo.tblERPInvoiceData ( SystemID ASC , CompanyID ASC ) INCLUDE ( [Number] ) WITH ( PAD_INDEX = OFF ,FILLFACTOR = 90 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ]
CREATE NONCLUSTERED INDEX [IX_tblERPInvoiceDataAmountIC] ON dbo.tblERPInvoiceData ( ClientID ASC , Amount ASC , InvoiceCredit ASC ) WITH ( PAD_INDEX = OFF ,FILLFACTOR = 90 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ]
CREATE NONCLUSTERED INDEX [IX_tblERPInvoiceData_Client_Sup_Invoice] ON dbo.tblERPInvoiceData ( ClientID ASC , SystemID ASC , CompanyID ASC , SupplierNumber ASC , InvoiceCredit ASC ) INCLUDE ( [Amount] , [Currency] , [DocumentDate] , [DocumentNumber] , [DtDocDate] , [ID] , [Number] , [Status] , [Year] ) WITH ( PAD_INDEX = OFF ,FILLFACTOR = 90 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ]
CREATE NONCLUSTERED INDEX [IX_tblERPInvoiceData_Am] ON dbo.tblERPInvoiceData ( DateUpdated ASC , Amount ASC , Number ASC , PaidDocument ASC ) WITH ( PAD_INDEX = OFF ,FILLFACTOR = 90 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ]
CREATE NONCLUSTERED INDEX [IX_TblERPinvoiceData_FileID1] ON dbo.tblERPInvoiceData ( FileID ASC ) INCLUDE ( [DocumentNumber] , [ID] ) WITH ( PAD_INDEX = OFF ,FILLFACTOR = 90 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ]

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s