|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Friday, May 18, 2012 5:29 PM
Points: 1,
Visits: 6
|
|
nvrm my post, i was not using the latest version!
And thx for the nice script!
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Wednesday, February 13, 2013 7:43 AM
Points: 1,
Visits: 15
|
|
Very handy script, thanks for your efforts.
Ali
|
|
|
|
|
SSC-Dedicated
           
Group: General Forum Members
Last Login: Today @ 6:46 PM
Points: 32,909,
Visits: 26,798
|
|
|
|
|
|
Old Hand
      
Group: General Forum Members
Last Login: Yesterday @ 9:15 AM
Points: 316,
Visits: 1,185
|
|
Jeff, The following lines in the SP exclude the primary key and any indexes that are created via adding a unique constraint rather than adding a unique index. AND SI.is_primary_key = 0 AND SI.is_unique_constraint = 0
|
|
|
|
|
SSC Veteran
      
Group: General Forum Members
Last Login: Wednesday, March 27, 2013 8:55 AM
Points: 289,
Visits: 683
|
|
|
|
|
|
SSC-Dedicated
           
Group: General Forum Members
Last Login: Today @ 6:46 PM
Points: 32,909,
Visits: 26,798
|
|
Jonathan AC Roberts (1/22/2013) Jeff, The following lines in the SP exclude the primary key and any indexes that are created via adding a unique constraint rather than adding a unique index. AND SI.is_primary_key = 0 AND SI.is_unique_constraint = 0
Sorry for the late feedback. Yes, I agree. I just can't imagine why anyone would do such a thing in a script titled "Script ALL Indexes".
--Jeff Moden "RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
|
|
|
|
|
Old Hand
      
Group: General Forum Members
Last Login: Yesterday @ 9:15 AM
Points: 316,
Visits: 1,185
|
|
Jeff Moden (2/8/2013)
Sorry for the late feedback. Yes, I agree. I just can't imagine why anyone would do such a thing in a script titled "Script ALL Indexes". Jeff, Yes a valid point, I've amended the procedure so it takes a table-name-pattern parameter so you can enter a table name with wildcards to generate index script for the only tables you want.
IF NOT EXISTS(SELECT NULL 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 NULL 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
/* 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
-- Usage: EXEC utils.GenerateIndexesScript '%O%', 1, 0, 0 Sample call utils.GenerateIndexesScript
Modifications 2012-May-04 R. Gosling 1) Added in the Schema name to table name
*/ ALTER PROCEDURE utils.GenerateIndexesScript ( @TableNamePattern sysname = '%', @IncludeFileGroup bit = 1, @IncludeDrop bit = 1, @IncludeFillFactor bit = 1 ) 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 OBJECT_NAME(SI.OBJECT_ID) LIKE @TableNamePattern 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)
-- 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.[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 BEGIN SET @sCreateSql = @sCreateSql + @NewLine + 'WITH (FillFactor = ' + CAST(@FillFactor AS varchar(13)) + ')' + @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 '-- **********************************************************************'
IF @IncludeDrop = 1 BEGIN PRINT @sDropSql PRINT 'GO' END --END IF
PRINT @sCreateSql PRINT 'GO' + @NewLine + @NewLine
FETCH NEXT FROM Indexes_cursor INTO @SchemaName, @TableName, @TableId, @IndexName, @IndexId, @FileGroupName, @FillFactor END --END WHILE CLOSE Indexes_cursor DEALLOCATE Indexes_cursor
END GO
|
|
|
|