This is a procedure that generates scripts for your indexes.
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
