Yes, you can do that by a few right click actions in Management Studio, but what if you need to automate it?
Included in this version-
- File output - needs OLE
- File Groups
- Drop Statements
- Fill Factor
- Compression
- Table Schemas
IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'utils' AND SCHEMA_OWNER = 'dbo') BEGIN EXEC('CREATE SCHEMA utils AUTHORIZATION dbo') END GO IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'usp_GenerateIndexesScript' AND ROUTINE_TYPE = N'PROCEDURE') BEGIN EXEC ('CREATE PROCEDURE [utils].[usp_GenerateIndexesScript] AS BEGIN SELECT 1 END') END GO /* Adapted from http://www.sqlservercentral.com/Forums/Topic401784-562-2.aspx (by Jonathan AC Roberts. ) Modifications 10/06/2010 By R.Doering - http://sqlsolace.blogspot.com 1) Changed Schema of routine to Utils 2) Changed Name from INFGenerateIndexesScript to GenerateIndexesScript 3) Added Schemas to script 4) Reformatted for clarity 5) Compression Option added -- Usage: EXEC utils.usp_GenerateIndexesScript @IncludeFileGroup = 1 ,@IncludeDrop = 1 ,@IncludeFillFactor = 1 ,@IncludeCompression = 1 ,@destinationparameter = '\\Server\sqlbackup$\Indexes\' */ IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'utils' AND SCHEMA_OWNER = 'dbo') BEGIN EXEC('CREATE SCHEMA utils AUTHORIZATION dbo') END GO IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'GenerateIndexesScript' AND ROUTINE_TYPE = N'PROCEDURE') BEGIN EXEC ('CREATE PROCEDURE [utils].[GenerateIndexesScript] AS BEGIN SELECT 1 END') END GO ALTER PROCEDURE utils.usp_GenerateIndexesScript ( @IncludeFileGroup bit = 1 ,@IncludeDrop bit = 1 ,@IncludeFillFactor bit = 1 ,@IncludeCompression bit = 1 ,@destinationparameter NVARCHAR(1000) = NULL ) AS BEGIN -- Get all existing indexes, but NOT the primary keys DECLARE Indexes_cursor CURSOR FOR SELECT SC.Name AS SchemaName , SO.Name AS TableName , SI.Object_Id AS TableId , SI.[Name] AS IndexName , SI.Index_ID AS IndexId , FG.[Name] AS FileGroupName , CASE WHEN SI.Fill_Factor = 0 THEN 100 ELSE SI.Fill_Factor END Fill_Factor FROM sys.indexes SI LEFT JOIN sys.filegroups FG ON SI.data_space_id = FG.data_space_id INNER JOIN sys.objects SO ON SI.object_id = SO.object_id INNER JOIN sys.schemas SC ON SC.schema_id = SO.schema_id WHERE ObjectProperty(SI.Object_Id, 'IsUserTable') = 1 AND SI.[Name] IS NOT NULL AND SI.is_primary_key = 0 AND SI.is_unique_constraint = 0 AND IndexProperty(SI.Object_Id, SI.[Name], 'IsStatistics') = 0 AND FG.[Name] IS NOT NULL ORDER BY Object_name(SI.Object_Id), SI.Index_ID DECLARE @SchemaName sysname DECLARE @TableName sysname DECLARE @TableId int DECLARE @IndexName sysname DECLARE @FileGroupName sysname DECLARE @IndexId int DECLARE @FillFactor int DECLARE @NewLine nvarchar(4000) SET @NewLine = CHAR(13) + CHAR(10) DECLARE @Tab nvarchar(4000) SET @Tab = Space(4) DECLARE @SQLOutput nvarchar(max) SET @SQLOutput = ' ' -- Loop through all indexes OPEN Indexes_cursor FETCH NEXT FROM Indexes_cursor INTO @SchemaName, @TableName, @TableId, @IndexName, @IndexId, @FileGroupName, @FillFactor WHILE (@@Fetch_Status = 0) BEGIN DECLARE @sIndexDesc nvarchar(4000) DECLARE @sCreateSql nvarchar(4000) DECLARE @sDropSql nvarchar(4000) SET @sIndexDesc = '-- Index ' + @IndexName + ' on table ' + @TableName SET @sDropSql = 'IF EXISTS(SELECT 1' + @NewLine + ' FROM sysindexes si' + @NewLine + ' INNER JOIN sysobjects so' + @NewLine + ' ON so.id = si.id' + @NewLine + ' WHERE si.[Name] = N''' + @IndexName + ''' -- Index Name' + @NewLine + ' AND so.[Name] = N''' + @TableName + ''') -- Table Name' + @NewLine + 'BEGIN' + @NewLine + ' DROP INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + ']' + @NewLine + 'END' + @NewLine SET @sCreateSql = 'CREATE ' -- Check if the index is unique IF (IndexProperty(@TableId, @IndexName, 'IsUnique') = 1) BEGIN SET @sCreateSql = @sCreateSql + 'UNIQUE ' END --END IF -- Check if the index is clustered IF (IndexProperty(@TableId, @IndexName, 'IsClustered') = 1) BEGIN SET @sCreateSql = @sCreateSql + 'CLUSTERED ' END --END IF SET @sCreateSql = @sCreateSql + 'INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + ']' + @NewLine + '(' + @NewLine -- Get all columns of the index DECLARE IndexColumns_cursor CURSOR FOR SELECT SC.[Name], IC.[is_included_column], IC.is_descending_key FROM sys.index_columns IC INNER JOIN sys.columns SC ON IC.Object_Id = SC.Object_Id AND IC.Column_ID = SC.Column_ID WHERE IC.Object_Id = @TableId AND Index_ID = @IndexId ORDER BY IC.key_ordinal DECLARE @IxColumn sysname DECLARE @IxIncl bit DECLARE @Desc bit DECLARE @IxIsIncl bit SET @IxIsIncl = 0 DECLARE @IxFirstColumn bit SET @IxFirstColumn = 1 -- Loop through all columns of the index and append them to the CREATE statement OPEN IndexColumns_cursor FETCH NEXT FROM IndexColumns_cursor INTO @IxColumn, @IxIncl, @Desc WHILE (@@Fetch_Status = 0) BEGIN IF (@IxFirstColumn = 1) BEGIN SET @IxFirstColumn = 0 END ELSE BEGIN --check to see if it's an included column IF (@IxIsIncl = 0) AND (@IxIncl = 1) BEGIN SET @IxIsIncl = 1 SET @sCreateSql = @sCreateSql + @NewLine + ')' + @NewLine + 'INCLUDE' + @NewLine + '(' + @NewLine END ELSE BEGIN SET @sCreateSql = @sCreateSql + ',' + @NewLine END --END IF END --END IF SET @sCreateSql = @sCreateSql + @Tab + '[' + @IxColumn + ']' -- check if ASC or DESC IF @IxIsIncl = 0 BEGIN IF @Desc = 1 BEGIN SET @sCreateSql = @sCreateSql + ' DESC' END ELSE BEGIN SET @sCreateSql = @sCreateSql + ' ASC' END --END IF END --END IF FETCH NEXT FROM IndexColumns_cursor INTO @IxColumn, @IxIncl, @Desc END --END WHILE CLOSE IndexColumns_cursor DEALLOCATE IndexColumns_cursor SET @sCreateSql = @sCreateSql + @NewLine + ') ' IF (@IncludeFillFactor = 1) OR (@IncludeCompression = 1) BEGIN SET @sCreateSql = @sCreateSql + @NewLine + 'WITH (' END IF @IncludeFillFactor = 1 BEGIN SET @sCreateSql = @sCreateSql + 'FillFactor = ' + Cast(@FillFactor as varchar(13)) END IF @IncludeCompression = 1 BEGIN IF @IncludeFillFactor = 1 BEGIN SET @sCreateSql = @sCreateSql + ',' END SET @sCreateSql = @sCreateSql + 'DATA_COMPRESSION = PAGE' END IF (@IncludeFillFactor = 1) OR (@IncludeCompression = 1) BEGIN SET @sCreateSql = @sCreateSql + ')' + @NewLine END --END IF IF @IncludeFileGroup = 1 BEGIN SET @sCreateSql = @sCreateSql + 'ON ['+ @FileGroupName + ']' + @NewLine END ELSE BEGIN SET @sCreateSql = @sCreateSql + @NewLine END --END IF PRINT '-- **********************************************************************' PRINT @sIndexDesc PRINT '-- **********************************************************************' SET @SQLOutput = @SQLOutput + '-- **********************************************************************' + @NewLine SET @SQLOutput = @SQLOutput + @sIndexDesc + @NewLine SET @SQLOutput = @SQLOutput + '-- **********************************************************************' + @NewLine SET @SQLOutput = @SQLOutput + @NewLine IF @IncludeDrop = 1 BEGIN PRINT @sDropSql PRINT 'GO' SET @SQLOutput = @SQLOutput + @sDropSql + @NewLine SET @SQLOutput = @SQLOutput + 'GO' + @NewLine END --END IF PRINT @sCreateSql PRINT 'GO' + @NewLine + @NewLine SET @SQLOutput = @SQLOutput + @sCreateSql + @NewLine SET @SQLOutput = @SQLOutput + 'GO' + @NewLine FETCH NEXT FROM Indexes_cursor INTO @SchemaName, @TableName, @TableId, @IndexName, @IndexId, @FileGroupName, @FillFactor END --END WHILE CLOSE Indexes_cursor DEALLOCATE Indexes_cursor -- @SQLOutput contains the output to place in a file IF LEN(@destinationparameter) > 0 BEGIN DECLARE @destinationfile varchar(500) DECLARE @destinationpath NVARCHAR(1000) DECLARE @as_at DATETIME DECLARE @databasename VARCHAR(100) DECLARE @servername VARCHAR(100) SET @servername = CAST(SERVERPROPERTY('MachineName') AS VARCHAR(100)) SET @databasename = CAST(DB_NAME() AS VARCHAR(100)) SET @as_at = GETDATE() SET @destinationpath = @destinationparameter + @servername EXEC sql_tools.Utils.usp_OLECreateFolder @newfolder = @destinationpath SET @destinationpath = @destinationpath + N'\' + @databasename EXEC sql_tools.Utils.usp_OLECreateFolder @newfolder = @destinationpath SET @destinationpath = @destinationpath + N'\' + REPLACE(REPLACE(CONVERT(NVARCHAR(20),@as_at,120),' ','.'),':','') EXEC sql_tools.Utils.usp_OLECreateFolder @newfolder = @destinationpath SET @destinationfile = @destinationpath + '\' + @databasename + '_Indexes_'+ REPLACE(REPLACE(CONVERT(NVARCHAR(20),@as_at,120),' ','.'),':','') + '.sql' EXEC sql_tools.utils.usp_OLEwritefile @FileName = @destinationfile , @TextData = @SQLOutput , @FileAction = 'CREATENEW' END --PRINT @SQLOutput END GO
You execute the procedure like this -
EXEC sql_tools.utils.usp_GenerateIndexesScript @IncludeFileGroup = 1 ,@IncludeDrop = 1 ,@IncludeFillFactor = 1 ,@IncludeCompression = 1 ,@destinationparameter = '\\SERVER07\sqlbackup$\Indexes\' GO