As part of a larger backup project I wanted to automatically script my non clustered indexes to a file and have set about writing a procedure to do this. Here are the steps to follow if you want to achieve the same.
1)
Enable OLE automation
2) Create
utils.usp_OLEwritefile (sp that Writes to a text file from TSQL)
3) Create utils.usp_GenerateIndexesScript as follows -
/*
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 with compression type
6) Existing Index detection script changed to use sys.indexes and sys.objects
7) Fix for Included Column Ordering
-- Usage:
EXEC utils.usp_GenerateIndexesScript
@IncludeFileGroup = 1
,@IncludeDrop = 1
,@IncludeFillFactor = 1
,@destinationparameter = '\\SERVER-01\sqlbackup$\Indexes\myindexes.sql'
,@IncludeCompression = 1
,@compressionType = 'PAGE'
*/
CREATE PROCEDURE [utils].[usp_GenerateIndexesScript]
(
@IncludeFileGroup bit = 1
,@IncludeDrop bit = 1
,@IncludeFillFactor bit = 1
,@destinationparameter NVARCHAR(1000) = NULL
,@IncludeCompression bit = 1
,@compressionType VARCHAR(4) = 'PAGE'
)
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 SC.Name , SO.Name ,SI.[Name]
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 ' + @SchemaName + '.' + @TableName
SET @sDropSql = 'IF EXISTS(SELECT 1' + @NewLine
+ ' FROM sys.indexes si' + @NewLine
+ ' INNER JOIN sys.objects so' + @NewLine
+ ' ON so.object_id = si.object_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.[is_included_column],
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 = ' + @compressionType
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
EXEC utils.usp_OLEwritefile @FileName = @destinationparameter
, @TextData = @SQLOutput
, @FileAction = 'CREATENEW'
END
--PRINT @SQLOutput
END
Run the procedure to script the indexes as follows -
The compression and destination file parameters are optional!
EXEC utils.usp_GenerateIndexesScript
@IncludeFileGroup = 1
,@IncludeDrop = 1
,@IncludeFillFactor = 1
,@destinationparameter = 'c:\MyNonClusteredIndexes.sql'
,@IncludeCompression = 1
,@compressionType = 'PAGE'
