SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Murali / Script all Indexes / Latest Posts

RE: Script all Indexes

Sridhar R Pailla — Thu, 02 May 2013 04:33:37 GMT

Thanks for the script it worked perfectly.

RE: Script all Indexes

Strix — Mon, 04 Mar 2013 03:58:47 GMT

I know this is an old post, but I would advise to be very careful with this script.In bol under indexproperty:IsPageLockDisallowed Page-locking value set by the ALLOW_PAGE_LOCKS option of ALTER INDEX. 1 = Page locking is disallowed. 0 = Page locking is allowed.Script statesIF (INDEXPROPERTY(@idxTableID, @idxname, 'IsPageLockDisallowed') = 1)SET @sParamSQL = @sParamSQL + ' ALLOW_PAGE_LOCKS = ON, 'ELSESET @sParamSQL = @sParamSQL + ' ALLOW_PAGE_LOCKS = OFF, 'This is the oposite and can cause serious performance loss (extra locking).

RE: Script all Indexes

shahmm — Fri, 24 Feb 2012 03:35:30 GMT

Here is the script after resolving minor issues with include and fill factor and added checking for index existence.Very nice script though thank you.DECLARE @idxTableName SYSNAMEDECLARE @idxTableID INTDECLARE @idxname SYSNAMEDECLARE @idxid INTDECLARE @colCount INTDECLARE @IxColumn SYSNAMEDECLARE @IxFirstColumn BITDECLARE @ColumnIDInTable INTDECLARE @ColumnIDInIndex INTDECLARE @IsIncludedColumn INTDECLARE @sIncludeCols VARCHAR(4000)DECLARE @sIndexCols VARCHAR(4000)DECLARE @sSQL VARCHAR(4000)DECLARE @rowcnt INTDECLARE @sParamSQL VARCHAR(4000)DECLARE @location SYSNAMEDECLARE @fillfactor INT-- Get all the index infoDECLARE curidx CURSORFORSELECT object_name(si.object_id) ,si.object_id ,si.NAME ,si.index_idFROM sys.indexes siLEFT JOIN information_schema.table_constraints tc ON si.NAME = tc.constraint_name AND object_name(si.object_id) = tc.table_nameWHERE objectproperty(si.object_id, 'IsUserTable') = 1ORDER BY object_name(si.object_id) ,si.index_idOPEN curidxFETCH NEXTFROM curidxINTO @idxTableName ,@idxTableID ,@idxname ,@idxid--loop WHILE (@@FETCH_STATUS = 0)BEGIN SET @sSQL = 'IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WHERE name = ''' + @idxname + ''')' + CHAR(13) SET @sSQL = @sSQL + 'BEGIN' + CHAR(13) SET @sSQL = @sSQL + 'CREATE ' -- Check if the index is unique IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsUnique') = 1) SET @sSQL = @sSQL + 'UNIQUE ' -- Check if the index is clustered IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsClustered') = 1) SET @sSQL = @sSQL + 'CLUSTERED ' SET @sSQL = @sSQL + 'INDEX ' + @idxname + ' ON ' + @idxTableName + CHAR(13) + '(' SET @sSQL = @sSQL + CHAR(13) SET @colCount = 0 SELECT @fillfactor = fill_factor FROM sys.indexes WHERE name = @idxname IF ISNULL(@fillfactor, 0) = 0 SET @fillfactor = 90 -- Get the number of cols in the index SELECT @colCount = COUNT(*) 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 = @idxtableid AND index_id = @idxid AND ic.is_included_column = 0 -- Get the file group info SELECT @location = f.[name] FROM sys.indexes i INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id] WHERE o.object_id = @idxTableID AND i.index_id = @idxid -- Get all columns of the index DECLARE curidxcolumn CURSOR FOR SELECT sc.column_id AS columnidintable ,sc.NAME ,ic.index_column_id columnidinindex ,ic.is_included_column AS isincludedcolumn 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 = @idxTableID AND index_id = @idxid ORDER BY ic.index_column_id SET @IxFirstColumn = 1 SET @sIncludeCols = '' SET @sIndexCols = '' SET @rowcnt = 0 OPEN curidxColumn FETCH NEXT FROM curidxColumn INTO @ColumnIDInTable ,@IxColumn ,@ColumnIDInIndex ,@IsIncludedColumn --loop WHILE (@@FETCH_STATUS = 0) BEGIN IF @IsIncludedColumn = 0 BEGIN SET @rowcnt = @rowcnt + 1 SET @sIndexCols = CHAR(9) + @sIndexCols + '[' + @IxColumn + ']' -- Check the sort order of the index cols IF (INDEXKEY_PROPERTY(@idxTableID, @idxid, @ColumnIDInIndex, 'IsDescending')) = 0 SET @sIndexCols = @sIndexCols + ' ASC ' ELSE SET @sIndexCols = @sIndexCols + ' DESC ' IF @rowcnt < @colCount SET @sIndexCols = @sIndexCols + ', ' END ELSE BEGIN -- Check for any include columns IF len(@sIncludeCols) > 0 SET @sIncludeCols = @sIncludeCols + ',' SET @sIncludeCols = @sIncludeCols + '[' + @IxColumn + ']' END FETCH NEXT FROM curidxColumn INTO @ColumnIDInTable ,@IxColumn ,@ColumnIDInIndex ,@IsIncludedColumn END CLOSE curidxColumn DEALLOCATE curidxColumn --append to the result IF LEN(@sIncludeCols) > 0 SET @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ') ' + ' INCLUDE ( ' + @sIncludeCols + ' ) ' ELSE SET @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ') ' -- Build the options SET @sParamSQL = ' WITH (FILLFACTOR = ' + cast(isnull(@fillfactor, 90) AS VARCHAR(3)) + ', ' --set @sParamSQL = ' WITH (' IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsPadIndex') = 1) SET @sParamSQL = @sParamSQL + ' PAD_INDEX = ON, ' ELSE SET @sParamSQL = @sParamSQL + ' PAD_INDEX = OFF, ' IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsPageLockDisallowed') = 1) SET @sParamSQL = @sParamSQL + ' ALLOW_PAGE_LOCKS = ON, ' ELSE SET @sParamSQL = @sParamSQL + ' ALLOW_PAGE_LOCKS = OFF, ' IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsRowLockDisallowed') = 1) SET @sParamSQL = @sParamSQL + ' ALLOW_ROW_LOCKS = ON, ' ELSE SET @sParamSQL = @sParamSQL + ' ALLOW_ROW_LOCKS = OFF, ' IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsStatistics') = 1) SET @sParamSQL = @sParamSQL + ' STATISTICS_NORECOMPUTE = ON, ' ELSE SET @sParamSQL = @sParamSQL + ' STATISTICS_NORECOMPUTE = OFF, ' SET @sParamSQL = @sParamSQL + ' DROP_EXISTING = ON ) ' SET @sIndexCols = @sIndexCols + CHAR(13) + @sParamSQL + ' ON [' + @location + ']' + CHAR(13) + 'END ' + CHAR(10) + 'GO' + CHAR(13) PRINT @sIndexCols FETCH NEXT FROM curidx INTO @idxTableName ,@idxTableID ,@idxname ,@idxidENDCLOSE curidxDEALLOCATE curidx

RE: Script all Indexes

SQL_Padre — Wed, 04 Jan 2012 08:08:50 GMT

I am having issues running this script against a SQL 2005 box (9.0.5000). I keep getting these errorsMsg 2715, Level 16, State 3, Line 280Column, parameter, or variable #1: Cannot find data type SYSNAME.Parameter or variable '@idxTableName' has an invalid data type.Msg 2715, Level 16, State 3, Line 280Column, parameter, or variable #3: Cannot find data type SYSNAME.Parameter or variable '@idxName' has an invalid data type.Msg 2715, Level 16, State 3, Line 280Column, parameter, or variable #7: Cannot find data type SYSNAME.Parameter or variable '@IxColumn' has an invalid data type.Msg 2715, Level 16, State 3, Line 280Column, parameter, or variable #17: Cannot find data type SYSNAME.Parameter or variable '@location' has an invalid data type.

RE: Script all Indexes

Lisa Olivieri — Fri, 04 Feb 2011 09:36:04 GMT

Hi, I changed the script (original script, not Jean-Marc's version) so that included columns are working. Here's what changed: 1) The section that builds included columns was using variable @IxColumn which was never set. 2) This select statement shown below in the original code was omitting included columns. It was changed to include them and now also includes a separate count called @colCountMinusIncludedColumns which is used to determine whether to add a comma to the list of index keys. Old code: SELECT @colCount = COUNT(*), 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 = @idxtableid AND index_id = @idxid AND ic.is_included_column = 0New code: SELECT @colCount = COUNT(*), @colCountMinusIncludedColumns = SUM(CASE ic.is_included_column WHEN 0 THEN 1 ELSE 0 END) 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 = @idxtableid AND index_id = @idxidHope this helps![code="sql"]-- Script out indexes completely, including both PK's and regular indexes, each clustered or nonclustered.-- DOES NOT HANDLE COMPRESSION; that's ok, since 2008 R2 RTM benchmarking shows it's faster and results in smaller indexes to insert uncompressed and then compress later-- HARDCODES [dbo] schema (i.e. it doesn't say [JohnDoe].[table], changing that to [dbo].[table]-- originally from http://www.sqlservercentral.com/Forums/Topic961088-2753-2.aspxSET NOCOUNT ONDECLARE@idxTableName SYSNAME,@idxTableID INT,@idxname SYSNAME,@idxid INT,@colCount INT,@colCountMinusIncludedColumns INT,@IxColumn SYSNAME,@IxFirstColumn BIT,@ColumnIDInTable INT,@ColumnIDInIndex INT,@IsIncludedColumn INT,@sIncludeCols VARCHAR(MAX),@sIndexCols VARCHAR(MAX),@sSQL VARCHAR(MAX),@sParamSQL VARCHAR(MAX),@sFilterSQL VARCHAR(MAX),@location SYSNAME,@IndexCount INT,@CurrentIndex INT,@CurrentCol INT,@Name VARCHAR(128),@IsPrimaryKey TINYINT,@Fillfactor INT,@FilterDefinition VARCHAR(MAX),@IsClustered BIT -- used solely for putting information into the result tableIF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].[#IndexSQL]'))DROP TABLE [dbo].[#IndexSQL]CREATE TABLE #IndexSQL( TableName VARCHAR(128) NOT NULL ,IndexName VARCHAR(128) NOT NULL ,IsClustered BIT NOT NULL ,IsPrimaryKey BIT NOT NULL ,IndexCreateSQL VARCHAR(max) NOT NULL)IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].[#IndexListing]'))DROP TABLE [dbo].[#IndexListing]CREATE TABLE #IndexListing([IndexListingID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,[TableName] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[ObjectID] INT NOT NULL,[IndexName] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[IndexID] INT NOT NULL,[IsPrimaryKey] TINYINT NOT NULL,[FillFactor] INT,[FilterDefinition] NVARCHAR(MAX) NULL)IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].[#ColumnListing]'))DROP TABLE [dbo].[#ColumnListing]CREATE TABLE #ColumnListing([ColumnListingID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,[ColumnIDInTable] INT NOT NULL,[Name] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[ColumnIDInIndex] INT NOT NULL,[IsIncludedColumn] BIT NULL)INSERT INTO #IndexListing( [TableName], [ObjectID], [IndexName], [IndexID], [IsPrimaryKey], [FILLFACTOR], [FilterDefinition] )SELECT OBJECT_NAME(si.object_id), si.object_id, si.name, si.index_id, si.Is_Primary_Key, si.Fill_Factor, si.filter_definitionFROM sys.indexes siLEFT OUTER JOIN information_schema.table_constraints tc ON si.name = tc.constraint_name AND OBJECT_NAME(si.object_id) = tc.table_nameWHERE OBJECTPROPERTY(si.object_id, 'IsUserTable') = 1ORDER BY OBJECT_NAME(si.object_id), si.index_idSELECT @IndexCount = @@ROWCOUNT, @CurrentIndex = 1WHILE @CurrentIndex <= @IndexCountBEGIN SELECT @idxTableName = [TableName], @idxTableID = [ObjectID], @idxname = [IndexName], @idxid = [IndexID], @IsPrimaryKey = [IsPrimaryKey], @FillFactor = [FILLFACTOR], @FilterDefinition = [FilterDefinition] FROM #IndexListing WHERE [IndexListingID] = @CurrentIndex -- So - it is either an index or a constraint -- Check if the index is unique IF (@IsPrimaryKey = 1) BEGIN SET @sSQL = 'ALTER TABLE [dbo].[' + @idxTableName + '] ADD CONSTRAINT [' + @idxname + '] PRIMARY KEY ' -- Check if the index is clustered IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsClustered') = 0) BEGIN SET @sSQL = @sSQL + 'NON' SET @IsClustered = 0 END ELSE BEGIN SET @IsClustered = 1 END SET @sSQL = @sSQL + 'CLUSTERED' + CHAR(13) + '(' + CHAR(13) END ELSE BEGIN SET @sSQL = 'CREATE ' -- Check if the index is unique IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsUnique') = 1) BEGIN SET @sSQL = @sSQL + 'UNIQUE ' END -- Check if the index is clustered IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsClustered') = 1) BEGIN SET @sSQL = @sSQL + 'CLUSTERED ' SET @IsClustered = 1 END ELSE BEGIN SET @IsClustered = 0 END SELECT @sSQL = @sSQL + 'INDEX [' + @idxname + '] ON [dbo].[' + @idxTableName + ']' + CHAR(13) + '(' + CHAR(13), @colCount = 0, @colCountMinusIncludedColumns = 0 END -- Get the nuthe mber of cols in the index SELECT @colCount = COUNT(*), @colCountMinusIncludedColumns = SUM(CASE ic.is_included_column WHEN 0 THEN 1 ELSE 0 END) 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 = @idxtableid AND index_id = @idxid -- Get the file group info SELECT @location = f.[name] FROM sys.indexes i INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id] WHERE o.object_id = @idxTableID AND i.index_id = @idxid -- Get all columns of the index INSERT INTO #ColumnListing( [ColumnIDInTable], [Name], [ColumnIDInIndex],[IsIncludedColumn] ) SELECT sc.column_id, sc.name, ic.index_column_id, ic.is_included_column 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 = @idxTableID AND index_id = @idxid ORDER BY ic.index_column_id IF @@ROWCOUNT > 0 BEGIN SELECT @CurrentCol = 1 SELECT @IxFirstColumn = 1, @sIncludeCols = '', @sIndexCols = '' WHILE @CurrentCol <= @ColCount BEGIN SELECT @ColumnIDInTable = ColumnIDInTable, @Name = Name, @ColumnIDInIndex = ColumnIDInIndex, @IsIncludedColumn = IsIncludedColumn FROM #ColumnListing WHERE [ColumnListingID] = @CurrentCol IF @IsIncludedColumn = 0 BEGIN SELECT @sIndexCols = CHAR(9) + @sIndexCols + '[' + @Name + '] ' -- Check the sort order of the index cols ???????? IF (INDEXKEY_PROPERTY (@idxTableID,@idxid,@ColumnIDInIndex,'IsDescending')) = 0 BEGIN SET @sIndexCols = @sIndexCols + ' ASC ' END ELSE BEGIN SET @sIndexCols = @sIndexCols + ' DESC ' END IF @CurrentCol < @colCountMinusIncludedColumns BEGIN SET @sIndexCols = @sIndexCols + ', ' END END ELSE BEGIN -- Check for any include columns IF LEN(@sIncludeCols) > 0 BEGIN SET @sIncludeCols = @sIncludeCols + ',' END SELECT @sIncludeCols = @sIncludeCols + '[' + @Name + ']' END SET @CurrentCol = @CurrentCol + 1 END TRUNCATE TABLE #ColumnListing --append to the result IF LEN(@sIncludeCols) > 0 SET @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ') ' + ' INCLUDE ( ' + @sIncludeCols + ' ) ' ELSE SET @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ') ' -- Add filtering IF @FilterDefinition IS NOT NULL SET @sFilterSQL = ' WHERE ' + @FilterDefinition + ' ' + CHAR(13) ELSE SET @sFilterSQL = '' -- Build the options SET @sParamSQL = 'WITH ( PAD_INDEX = ' IF INDEXPROPERTY(@idxTableID, @idxname, 'IsPadIndex') = 1 SET @sParamSQL = @sParamSQL + 'ON,' ELSE SET @sParamSQL = @sParamSQL + 'OFF,' SET @sParamSQL = @sParamSQL + ' ALLOW_PAGE_LOCKS = ' IF INDEXPROPERTY(@idxTableID, @idxname, 'IsPageLockDisallowed') = 0 SET @sParamSQL = @sParamSQL + 'ON,' ELSE SET @sParamSQL = @sParamSQL + 'OFF,' SET @sParamSQL = @sParamSQL + ' ALLOW_ROW_LOCKS = ' IF INDEXPROPERTY(@idxTableID, @idxname, 'IsRowLockDisallowed') = 0 SET @sParamSQL = @sParamSQL + 'ON,' ELSE SET @sParamSQL = @sParamSQL + 'OFF,' SET @sParamSQL = @sParamSQL + ' STATISTICS_NORECOMPUTE = ' -- THIS DOES NOT WORK PROPERLY; IsStatistics only says what generated the last set, not what it was set to do. IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsStatistics') = 1) SET @sParamSQL = @sParamSQL + 'ON' ELSE SET @sParamSQL = @sParamSQL + 'OFF' -- Fillfactor 0 is actually not a valid percentage on SQL 2008 R2 IF ISNULL( @FillFactor, 90 ) <> 0 SET @sParamSQL = @sParamSQL + ' ,FILLFACTOR = ' + CAST( ISNULL( @FillFactor, 90 ) AS VARCHAR(3) ) IF (@IsPrimaryKey = 1) -- DROP_EXISTING isn't valid for PK's BEGIN SET @sParamSQL = @sParamSQL + ' ) ' END ELSE BEGIN SET @sParamSQL = @sParamSQL + ' ,DROP_EXISTING = ON ) ' END SET @sSQL = @sIndexCols + CHAR(13) + @sFilterSQL + CHAR(13) + @sParamSQL -- 2008 R2 allows ON [filegroup] for primary keys as well, negating the old "IF THE INDEX IS NOT A PRIMARY KEY - ADD THIS - ELSE DO NOT" IsPrimaryKey IF statement SET @sSQL = @sSQL + ' ON [' + @location + ']' --PRINT @sIndexCols + CHAR(13) INSERT INTO #IndexSQL (TableName, IndexName, IsClustered, IsPrimaryKey, IndexCreateSQL) VALUES (@idxTableName, @idxName, @IsClustered, @IsPrimaryKey, @sSQL) END SET @CurrentIndex = @CurrentIndex + 1ENDSELECT * FROM #IndexSQL[/code]

RE: Script all Indexes

Lisa Olivieri — Fri, 04 Feb 2011 07:05:22 GMT

Wow, thanks for the quick reply! I copied the script posted above by Jean-Marc Burgstahler on 10/16/2010 and made no changes to it. I also tried copying the script found here - http://www.sqlservercentral.com/scripts/Indexing/70737/ - and got the same results, no included columns.

RE: Script all Indexes

murali.Jillellamudi — Fri, 04 Feb 2011 06:45:59 GMT

Lisa Can you please send me the source code you are using to generate the script , let me check and comeback to you.RegardsMurali

RE: Script all Indexes

Lisa Olivieri — Fri, 04 Feb 2011 06:40:08 GMT

Hi, I'm so excited about this code! However, I'm not getting included columns in the create index statement. I'm running SQL 2008 R2.For example, here's what I'm getting from the script:CREATE INDEX [IX_xyz] ON [dbo].[Table] ( [Key1] ASC ) WITH ( PAD_INDEX = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, STATISTICS_NORECOMPUTE = OFF ,DROP_EXISTING = ON ) ON [PRIMARY]Here's what I get from scripting from SSMS:CREATE NONCLUSTERED INDEX [IX_xyz] N [dbo].[Table] ( [Key1] ASC)[b]INCLUDE [/b]( [DebitOrCredit],[GroupCurrencyAmount],[LocalAmount],[TransactionAmount]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

RE: Script all Indexes

Douglas Osborne-456728 — Mon, 18 Oct 2010 07:29:07 GMT

Nice Add Jean!

RE: Script all Indexes

BURGSTAHLER Jean-Marc — Sat, 16 Oct 2010 03:05:05 GMT

Hi,Great script.I've done minor changes to include :- schema- drop script- commented filter_definition for SQL 2005- Added the ability to generate only for one table (@TableToScript='<your table>' and @SchemaToScript='<your schema>', for all, set them to NULL)[code="sql"]-- Script out indexes completely, including both PK's and regular indexes, each clustered or nonclustered.-- DOES NOT HANDLE COMPRESSION; that's ok, since 2008 R2 RTM benchmarking shows it's faster and results in smaller indexes to insert uncompressed and then compress later-- HARDCODES [dbo] schema (i.e. it doesn't say [JohnDoe].[table], changing that to [dbo].[table]-- originally from http://www.sqlservercentral.com/Forums/Topic961088-2753-2.aspxDECLARE@IdxSchema SYSNAME,@idxTableName SYSNAME,@idxTableID INT,@idxname SYSNAME,@idxid INT,@colCount INT,@IxColumn SYSNAME,@IxFirstColumn BIT,@ColumnIDInTable INT,@ColumnIDInIndex INT,@IsIncludedColumn INT,@sIncludeCols VARCHAR(MAX),@sIndexCols VARCHAR(MAX),@sSQL VARCHAR(MAX),@sSQLDrop VARCHAR(MAX),@sParamSQL VARCHAR(MAX),@sFilterSQL VARCHAR(MAX),@location SYSNAME,@IndexCount INT,@CurrentIndex INT,@CurrentCol INT,@Name VARCHAR(128),@IsPrimaryKey TINYINT,@Fillfactor INT,@FilterDefinition VARCHAR(MAX),@IsClustered BIT, -- used solely for putting information into the result table@TableToScript SYSNAME,@SchemaToScript SYSNAMESET @TableToScript=NULLSET @SchemaToScript=NULLIF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].[#IndexSQL]'))DROP TABLE [dbo].[#IndexSQL]CREATE TABLE #IndexSQL( SchemaName VARCHAR(128) NOT NULL ,TableName VARCHAR(128) NOT NULL ,IndexName VARCHAR(128) NOT NULL ,IsClustered BIT NOT NULL ,IsPrimaryKey BIT NOT NULL ,IndexCreateSQL VARCHAR(max) NOT NULL ,IndexDropSQL VARCHAR(max) NOT NULL)IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].[#IndexListing]'))DROP TABLE [dbo].[#IndexListing]CREATE TABLE #IndexListing([IndexListingID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,[SchemaName] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[TableName] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[ObjectID] INT NOT NULL,[IndexName] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[IndexID] INT NOT NULL,[IsPrimaryKey] TINYINT NOT NULL,[FillFactor] INT,[FilterDefinition] NVARCHAR(MAX) NULL)IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].[#ColumnListing]'))DROP TABLE [dbo].[#ColumnListing]CREATE TABLE #ColumnListing([ColumnListingID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,[ColumnIDInTable] INT NOT NULL,[Name] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[ColumnIDInIndex] INT NOT NULL,[IsIncludedColumn] BIT NULL)IF ISNULL(@TableToScript,'')=''BEGIN INSERT INTO #IndexListing( [SchemaName], [TableName], [ObjectID], [IndexName], [IndexID], [IsPrimaryKey], [FILLFACTOR], [FilterDefinition] ) SELECT ss.name, OBJECT_NAME(si.object_id), si.object_id, si.name, si.index_id, si.Is_Primary_Key, si.Fill_Factor, NULL --si.filter_definition FROM sys.indexes si LEFT OUTER JOIN information_schema.table_constraints tc ON si.name = tc.constraint_name AND OBJECT_NAME(si.object_id) = tc.table_name INNER JOIN sys.objects so ON so.object_id=si.object_id INNER JOIN sys.schemas ss ON ss.schema_id=so.schema_id WHERE OBJECTPROPERTY(si.object_id, 'IsUserTable') = 1 ORDER BY OBJECT_NAME(si.object_id), si.index_idENDELSEBEGIN INSERT INTO #IndexListing( [SchemaName], [TableName], [ObjectID], [IndexName], [IndexID], [IsPrimaryKey], [FILLFACTOR], [FilterDefinition] ) SELECT ss.name, OBJECT_NAME(si.object_id), si.object_id, si.name, si.index_id, si.Is_Primary_Key, si.Fill_Factor, NULL --si.filter_definition FROM sys.indexes si LEFT OUTER JOIN information_schema.table_constraints tc ON si.name = tc.constraint_name AND OBJECT_NAME(si.object_id) = tc.table_name INNER JOIN sys.objects so ON so.object_id=si.object_id INNER JOIN sys.schemas ss ON so.schema_id=ss.schema_id WHERE OBJECTPROPERTY(si.object_id, 'IsUserTable') = 1 AND OBJECT_NAME(si.OBJECT_ID)=@TableToScript and ss.name=@SchemaToScript ORDER BY OBJECT_NAME(si.object_id), si.index_idENDSELECT @IndexCount = @@ROWCOUNT, @CurrentIndex = 1WHILE @CurrentIndex <= @IndexCountBEGINSELECT @IdxSchema=[SchemaName],@idxTableName = [TableName],@idxTableID = [ObjectID],@idxname = [IndexName],@idxid = [IndexID],@IsPrimaryKey = [IsPrimaryKey],@FillFactor = [FILLFACTOR],@FilterDefinition = [FilterDefinition]FROM #IndexListingWHERE [IndexListingID] = @CurrentIndex-- So - it is either an index or a constraint-- Check if the index is uniqueIF (@IsPrimaryKey = 1)BEGIN SET @sSQL = 'ALTER TABLE ['+@IdxSchema+'].[' + @idxTableName + '] ADD CONSTRAINT [' + @idxname + '] PRIMARY KEY ' SET @sSQLDrop='ALTER TABLE ['+@IdxSchema+'].[' + @idxTableName + '] DROP CONSTRAINT [' + @idxname + ']' -- Check if the index is clustered IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsClustered') = 0) BEGIN SET @sSQL = @sSQL + 'NON' SET @IsClustered = 0 END ELSE BEGIN SET @IsClustered = 1 END SET @sSQL = @sSQL + 'CLUSTERED' + CHAR(13) + '(' + CHAR(13)ENDELSEBEGIN SET @sSQL = 'CREATE ' SET @sSQLDrop = 'DROP INDEX [' + @idxname + '] ON ['+@IdxSchema+'].[' + @idxTableName + ']' -- Check if the index is unique IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsUnique') = 1) BEGIN SET @sSQL = @sSQL + 'UNIQUE ' END -- Check if the index is clustered IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsClustered') = 1) BEGIN SET @sSQL = @sSQL + 'CLUSTERED ' SET @IsClustered = 1 END ELSE BEGIN SET @IsClustered = 0 END SELECT @sSQL = @sSQL + 'INDEX [' + @idxname + '] ON ['+@IdxSchema+'].[' + @idxTableName + ']' + CHAR(13) + '(' + CHAR(13), @colCount = 0END-- Get the number of cols in the indexSELECT @colCount = COUNT(*)FROM sys.index_columns icINNER JOIN sys.columns sc ON ic.object_id = sc.object_id AND ic.column_id = sc.column_idWHERE ic.object_id = @idxtableid AND index_id = @idxid AND ic.is_included_column = 0-- Get the file group infoSELECT @location = f.[name]FROM sys.indexes iINNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_idINNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id]WHERE o.object_id = @idxTableID AND i.index_id = @idxid-- Get all columns of the indexINSERT INTO #ColumnListing( [ColumnIDInTable], [Name], [ColumnIDInIndex],[IsIncludedColumn] )SELECT sc.column_id, sc.name, ic.index_column_id, ic.is_included_columnFROM sys.index_columns icINNER JOIN sys.columns sc ON ic.object_id = sc.object_id AND ic.column_id = sc.column_idWHERE ic.object_id = @idxTableID AND index_id = @idxidORDER BY ic.index_column_idIF @@ROWCOUNT > 0BEGINSELECT @CurrentCol = 1SELECT @IxFirstColumn = 1, @sIncludeCols = '', @sIndexCols = ''WHILE @CurrentCol <= @ColCountBEGINSELECT @ColumnIDInTable = ColumnIDInTable,@Name = Name,@ColumnIDInIndex = ColumnIDInIndex,@IsIncludedColumn = IsIncludedColumnFROM #ColumnListingWHERE [ColumnListingID] = @CurrentColIF @IsIncludedColumn = 0BEGINSET @sIndexCols = CHAR(9) + @sIndexCols + '[' + @Name + '] '-- Check the sort order of the index cols ????????IF (INDEXKEY_PROPERTY (@idxTableID,@idxid,@ColumnIDInIndex,'IsDescending')) = 0BEGINSET @sIndexCols = @sIndexCols + ' ASC 'ENDELSEBEGINSET @sIndexCols = @sIndexCols + ' DESC 'ENDIF @CurrentCol < @colCountBEGINSET @sIndexCols = @sIndexCols + ', 'ENDENDELSEBEGIN-- Check for any include columnsIF LEN(@sIncludeCols) > 0BEGINSET @sIncludeCols = @sIncludeCols + ','ENDSET @sIncludeCols = @sIncludeCols + '[' + @IxColumn + ']'ENDSET @CurrentCol = @CurrentCol + 1ENDTRUNCATE TABLE #ColumnListing--append to the resultIF LEN(@sIncludeCols) > 0 SET @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ') ' + ' INCLUDE ( ' + @sIncludeCols + ' ) 'ELSE SET @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ') '-- Add filteringIF @FilterDefinition IS NOT NULL SET @sFilterSQL = ' WHERE ' + @FilterDefinition + ' ' + CHAR(13)ELSE SET @sFilterSQL = ''-- Build the optionsSET @sParamSQL = 'WITH ( PAD_INDEX = 'IF INDEXPROPERTY(@idxTableID, @idxname, 'IsPadIndex') = 1 SET @sParamSQL = @sParamSQL + 'ON,'ELSE SET @sParamSQL = @sParamSQL + 'OFF,'SET @sParamSQL = @sParamSQL + ' ALLOW_PAGE_LOCKS = 'IF INDEXPROPERTY(@idxTableID, @idxname, 'IsPageLockDisallowed') = 0 SET @sParamSQL = @sParamSQL + 'ON,'ELSE SET @sParamSQL = @sParamSQL + 'OFF,'SET @sParamSQL = @sParamSQL + ' ALLOW_ROW_LOCKS = 'IF INDEXPROPERTY(@idxTableID, @idxname, 'IsRowLockDisallowed') = 0SET @sParamSQL = @sParamSQL + 'ON,'ELSESET @sParamSQL = @sParamSQL + 'OFF,'SET @sParamSQL = @sParamSQL + ' STATISTICS_NORECOMPUTE = '-- THIS DOES NOT WORK PROPERLY; IsStatistics only says what generated the last set, not what it was set to do.IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsStatistics') = 1)SET @sParamSQL = @sParamSQL + 'ON'ELSESET @sParamSQL = @sParamSQL + 'OFF'-- Fillfactor 0 is actually not a valid percentage on SQL 2008 R2IF ISNULL( @FillFactor, 90 ) <> 0 SET @sParamSQL = @sParamSQL + ' ,FILLFACTOR = ' + CAST( ISNULL( @FillFactor, 90 ) AS VARCHAR(3) )IF (@IsPrimaryKey = 1) -- DROP_EXISTING isn't valid for PK'sBEGIN SET @sParamSQL = @sParamSQL + ' ) 'ENDELSEBEGIN SET @sParamSQL = @sParamSQL + ' ,DROP_EXISTING = ON ) 'ENDSET @sSQL = @sIndexCols + CHAR(13) + @sFilterSQL + CHAR(13) + @sParamSQL-- 2008 R2 allows ON [filegroup] for primary keys as well, negating the old "IF THE INDEX IS NOT A PRIMARY KEY - ADD THIS - ELSE DO NOT" IsPrimaryKey IF statementSET @sSQL = @sSQL + ' ON [' + @location + ']'--PRINT @sIndexCols + CHAR(13)INSERT INTO #IndexSQL (SchemaName, TableName, IndexName, IsClustered, IsPrimaryKey, IndexCreateSQL, IndexDropSQL) VALUES (@IdxSchema, @idxTableName, @idxName, @IsClustered, @IsPrimaryKey, @sSQL, @sSQLDrop)ENDSET @CurrentIndex = @CurrentIndex + 1ENDSELECT * FROM #IndexSQL ORDER BY 1,2 --WHERE IsPrimaryKey=0[/code]Jean-Marc

RE: Script all Indexes

murali.Jillellamudi — Wed, 01 Sep 2010 04:21:41 GMT

Sorry Guys I was on a vacation so could not amend the script with the necessary changes. comments and enhancements are much appreciated. I am glad that the script is quite helpful to our sql community.RegardsMurali

RE: Script all Indexes

Nadrek — Mon, 30 Aug 2010 08:48:25 GMT

To cross-pollinate this article discussion with a closely related article that includes a very simple SQL statement for limited scripting of SQL 2005 indexes:[url=http://www.sqlservercentral.com/scripts/T-SQL/71058/]http://www.sqlservercentral.com/scripts/T-SQL/71058/[/url]

RE: Script all Indexes

Nadrek — Fri, 27 Aug 2010 12:45:35 GMT

EDITED - minor IsClustered bug fixedAlteration in the middle - the "Disallowed" requires a double negation logic:[code]IF INDEXPROPERTY(@idxTableID, @idxname, 'IsPageLockDisallowed') = 0 SET @sParamSQL = @sParamSQL + 'ON,'ELSE SET @sParamSQL = @sParamSQL + 'OFF,'SET @sParamSQL = @sParamSQL + ' ALLOW_ROW_LOCKS = 'IF INDEXPROPERTY(@idxTableID, @idxname, 'IsRowLockDisallowed') = 0SET @sParamSQL = @sParamSQL + 'ON,'ELSESET @sParamSQL = @sParamSQL + 'OFF,'[/code]NOTE: Statistics_norecompute does not show OFF work if you just re-created an index with the setting to OFF.I've altered this to generate a #temp table for use in further scripting, rather than printed output, as well as to work with filtered indexes, and to use varchar(max) instead of varchar(4000). Primary keys now get the ON [filegroup] as well (which works on 2008 R2). All #temp tables are conditionally removed each run.Exercises for the reader:Conversion to purely set-based methodology.Another column with DROP statements.Another column with the snippet to create primary keys as part of a CREATE TABLE statement.[code]-- Script out indexes completely, including both PK's and regular indexes, each clustered or nonclustered.-- DOES NOT HANDLE COMPRESSION; that's ok, since 2008 R2 RTM benchmarking shows it's faster and results in smaller indexes to insert uncompressed and then compress later-- HARDCODES [dbo] schema (i.e. it doesn't say [JohnDoe].[table], changing that to [dbo].[table]-- originally from http://www.sqlservercentral.com/Forums/Topic961088-2753-2.aspxDECLARE@idxTableName SYSNAME,@idxTableID INT,@idxname SYSNAME,@idxid INT,@colCount INT,@IxColumn SYSNAME,@IxFirstColumn BIT,@ColumnIDInTable INT,@ColumnIDInIndex INT,@IsIncludedColumn INT,@sIncludeCols VARCHAR(MAX),@sIndexCols VARCHAR(MAX),@sSQL VARCHAR(MAX),@sParamSQL VARCHAR(MAX),@sFilterSQL VARCHAR(MAX),@location SYSNAME,@IndexCount INT,@CurrentIndex INT,@CurrentCol INT,@Name VARCHAR(128),@IsPrimaryKey TINYINT,@Fillfactor INT,@FilterDefinition VARCHAR(MAX),@IsClustered BIT -- used solely for putting information into the result tableIF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].[#IndexSQL]'))DROP TABLE [dbo].[#IndexSQL]CREATE TABLE #IndexSQL( TableName VARCHAR(128) NOT NULL ,IndexName VARCHAR(128) NOT NULL ,IsClustered BIT NOT NULL ,IsPrimaryKey BIT NOT NULL ,IndexCreateSQL VARCHAR(max) NOT NULL)IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].[#IndexListing]'))DROP TABLE [dbo].[#IndexListing]CREATE TABLE #IndexListing([IndexListingID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,[TableName] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[ObjectID] INT NOT NULL,[IndexName] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[IndexID] INT NOT NULL,[IsPrimaryKey] TINYINT NOT NULL,[FillFactor] INT,[FilterDefinition] NVARCHAR(MAX) NULL)IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].[#ColumnListing]'))DROP TABLE [dbo].[#ColumnListing]CREATE TABLE #ColumnListing([ColumnListingID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,[ColumnIDInTable] INT NOT NULL,[Name] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[ColumnIDInIndex] INT NOT NULL,[IsIncludedColumn] BIT NULL)INSERT INTO #IndexListing( [TableName], [ObjectID], [IndexName], [IndexID], [IsPrimaryKey], [FILLFACTOR], [FilterDefinition] )SELECT OBJECT_NAME(si.object_id), si.object_id, si.name, si.index_id, si.Is_Primary_Key, si.Fill_Factor, si.filter_definitionFROM sys.indexes siLEFT OUTER JOIN information_schema.table_constraints tc ON si.name = tc.constraint_name AND OBJECT_NAME(si.object_id) = tc.table_nameWHERE OBJECTPROPERTY(si.object_id, 'IsUserTable') = 1ORDER BY OBJECT_NAME(si.object_id), si.index_idSELECT @IndexCount = @@ROWCOUNT, @CurrentIndex = 1WHILE @CurrentIndex <= @IndexCountBEGINSELECT @idxTableName = [TableName],@idxTableID = [ObjectID],@idxname = [IndexName],@idxid = [IndexID],@IsPrimaryKey = [IsPrimaryKey],@FillFactor = [FILLFACTOR],@FilterDefinition = [FilterDefinition]FROM #IndexListingWHERE [IndexListingID] = @CurrentIndex-- So - it is either an index or a constraint-- Check if the index is uniqueIF (@IsPrimaryKey = 1)BEGIN SET @sSQL = 'ALTER TABLE [dbo].[' + @idxTableName + '] ADD CONSTRAINT [' + @idxname + '] PRIMARY KEY ' -- Check if the index is clustered IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsClustered') = 0) BEGIN SET @sSQL = @sSQL + 'NON' SET @IsClustered = 0 END ELSE BEGIN SET @IsClustered = 1 END SET @sSQL = @sSQL + 'CLUSTERED' + CHAR(13) + '(' + CHAR(13)ENDELSEBEGIN SET @sSQL = 'CREATE ' -- Check if the index is unique IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsUnique') = 1) BEGIN SET @sSQL = @sSQL + 'UNIQUE ' END -- Check if the index is clustered IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsClustered') = 1) BEGIN SET @sSQL = @sSQL + 'CLUSTERED ' SET @IsClustered = 1 END ELSE BEGIN SET @IsClustered = 0 END SELECT @sSQL = @sSQL + 'INDEX [' + @idxname + '] ON [dbo].[' + @idxTableName + ']' + CHAR(13) + '(' + CHAR(13), @colCount = 0END-- Get the number of cols in the indexSELECT @colCount = COUNT(*)FROM sys.index_columns icINNER JOIN sys.columns sc ON ic.object_id = sc.object_id AND ic.column_id = sc.column_idWHERE ic.object_id = @idxtableid AND index_id = @idxid AND ic.is_included_column = 0-- Get the file group infoSELECT @location = f.[name]FROM sys.indexes iINNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_idINNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id]WHERE o.object_id = @idxTableID AND i.index_id = @idxid-- Get all columns of the indexINSERT INTO #ColumnListing( [ColumnIDInTable], [Name], [ColumnIDInIndex],[IsIncludedColumn] )SELECT sc.column_id, sc.name, ic.index_column_id, ic.is_included_columnFROM sys.index_columns icINNER JOIN sys.columns sc ON ic.object_id = sc.object_id AND ic.column_id = sc.column_idWHERE ic.object_id = @idxTableID AND index_id = @idxidORDER BY ic.index_column_idIF @@ROWCOUNT > 0BEGINSELECT @CurrentCol = 1SELECT @IxFirstColumn = 1, @sIncludeCols = '', @sIndexCols = ''WHILE @CurrentCol <= @ColCountBEGINSELECT @ColumnIDInTable = ColumnIDInTable,@Name = Name,@ColumnIDInIndex = ColumnIDInIndex,@IsIncludedColumn = IsIncludedColumnFROM #ColumnListingWHERE [ColumnListingID] = @CurrentColIF @IsIncludedColumn = 0BEGINSET @sIndexCols = CHAR(9) + @sIndexCols + '[' + @Name + '] '-- Check the sort order of the index cols ????????IF (INDEXKEY_PROPERTY (@idxTableID,@idxid,@ColumnIDInIndex,'IsDescending')) = 0BEGINSET @sIndexCols = @sIndexCols + ' ASC 'ENDELSEBEGINSET @sIndexCols = @sIndexCols + ' DESC 'ENDIF @CurrentCol < @colCountBEGINSET @sIndexCols = @sIndexCols + ', 'ENDENDELSEBEGIN-- Check for any include columnsIF LEN(@sIncludeCols) > 0BEGINSET @sIncludeCols = @sIncludeCols + ','ENDSET @sIncludeCols = @sIncludeCols + '[' + @IxColumn + ']'ENDSET @CurrentCol = @CurrentCol + 1ENDTRUNCATE TABLE #ColumnListing--append to the resultIF LEN(@sIncludeCols) > 0 SET @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ') ' + ' INCLUDE ( ' + @sIncludeCols + ' ) 'ELSE SET @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ') '-- Add filteringIF @FilterDefinition IS NOT NULL SET @sFilterSQL = ' WHERE ' + @FilterDefinition + ' ' + CHAR(13)ELSE SET @sFilterSQL = ''-- Build the optionsSET @sParamSQL = 'WITH ( PAD_INDEX = 'IF INDEXPROPERTY(@idxTableID, @idxname, 'IsPadIndex') = 1 SET @sParamSQL = @sParamSQL + 'ON,'ELSE SET @sParamSQL = @sParamSQL + 'OFF,'SET @sParamSQL = @sParamSQL + ' ALLOW_PAGE_LOCKS = 'IF INDEXPROPERTY(@idxTableID, @idxname, 'IsPageLockDisallowed') = 0 SET @sParamSQL = @sParamSQL + 'ON,'ELSE SET @sParamSQL = @sParamSQL + 'OFF,'SET @sParamSQL = @sParamSQL + ' ALLOW_ROW_LOCKS = 'IF INDEXPROPERTY(@idxTableID, @idxname, 'IsRowLockDisallowed') = 0SET @sParamSQL = @sParamSQL + 'ON,'ELSESET @sParamSQL = @sParamSQL + 'OFF,'SET @sParamSQL = @sParamSQL + ' STATISTICS_NORECOMPUTE = '-- THIS DOES NOT WORK PROPERLY; IsStatistics only says what generated the last set, not what it was set to do.IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsStatistics') = 1)SET @sParamSQL = @sParamSQL + 'ON'ELSESET @sParamSQL = @sParamSQL + 'OFF'-- Fillfactor 0 is actually not a valid percentage on SQL 2008 R2IF ISNULL( @FillFactor, 90 ) <> 0 SET @sParamSQL = @sParamSQL + ' ,FILLFACTOR = ' + CAST( ISNULL( @FillFactor, 90 ) AS VARCHAR(3) )IF (@IsPrimaryKey = 1) -- DROP_EXISTING isn't valid for PK'sBEGIN SET @sParamSQL = @sParamSQL + ' ) 'ENDELSEBEGIN SET @sParamSQL = @sParamSQL + ' ,DROP_EXISTING = ON ) 'ENDSET @sSQL = @sIndexCols + CHAR(13) + @sFilterSQL + CHAR(13) + @sParamSQL-- 2008 R2 allows ON [filegroup] for primary keys as well, negating the old "IF THE INDEX IS NOT A PRIMARY KEY - ADD THIS - ELSE DO NOT" IsPrimaryKey IF statementSET @sSQL = @sSQL + ' ON [' + @location + ']'--PRINT @sIndexCols + CHAR(13)INSERT INTO #IndexSQL (TableName, IndexName, IsClustered, IsPrimaryKey, IndexCreateSQL) VALUES (@idxTableName, @idxName, @IsClustered, @IsPrimaryKey, @sSQL)ENDSET @CurrentIndex = @CurrentIndex + 1END--SELECT * FROM #IndexSQL[/code]

RE: Script all Indexes

Douglas Osborne-456728 — Fri, 27 Aug 2010 11:48:57 GMT

Very nice followup - good add!Doug

RE: Script all Indexes

Nadrek — Fri, 27 Aug 2010 11:24:23 GMT

Minor alterations at the end:-- Commas removed from the end of ON and OFF so variable following arguments work properly with leading commasIF (INDEXPROPERTY(@idxTableID, @idxname, 'IsStatistics') = 1)SET @sParamSQL = @sParamSQL + 'ON'ELSESET @sParamSQL = @sParamSQL + 'OFF'-- Fillfactor 0 is actually not a valid percentage on SQL 2008 R2IF ISNULL( @FillFactor, 90 ) <> 0 SET @sParamSQL = @sParamSQL + ' ,FILLFACTOR = ' + CAST( ISNULL( @FillFactor, 90 ) AS VARCHAR(3) )IF (@IsPrimaryKey = 1) -- DROP_EXISTING isn't valid for PK'sBEGIN SET @sParamSQL = @sParamSQL + ' ) 'ENDELSEBEGIN SET @sParamSQL = @sParamSQL + ' ,DROP_EXISTING = ON ) 'ENDSET @sIndexCols = @sIndexCols + CHAR(13) + @sParamSQL-- IF THE INDEX IS NOT A PRIMARY KEY - ADD THIS - ELSE DO NOTIF (@IsPrimaryKey = 0)BEGINSET @sIndexCols = @sIndexCols + ' ON [' + @location + ']'END

RE: Script all Indexes

Nadrek — Fri, 27 Aug 2010 11:07:47 GMT

This is wonderful!But does anyone know how to figure out what the DATA_COMPRESSION setting is for a given index (none, row, or page)? It doesn't appear to be in sys.indexes or the most common property functions; the closest I've seen is compressed_page_count in sys.dm_db_index_physical_stats with 'SAMPLED' or 'DETAILED' level (clearly a bad idea to use on a large database).

RE: Script all Indexes

eric-470517 — Tue, 24 Aug 2010 13:05:55 GMT

I had a problem with the script generating the drop statements for my clustered and nonclustered indexs that are not Primary Keys.The following statement would not return the object id and therefore would use the previous indexes information when it did the print statement for @sdropsqlso.object_id = object_id ( @idxTableName) and si.index_id = @idxid and si.type IN ( 1,2) -- is_primary_key = 0 I changed the @idxTableName to the actual object id, @idxTableID and it worked. Upon further investigation If I added the schema name in front of the @idxTableName it would also work correctly.

RE: Script all Indexes

Douglas Osborne-456728 — Fri, 06 Aug 2010 06:44:53 GMT

I tweaked your code a bit:CREATE PROCEDURE [dbo].[IndexLister]ASBEGIN SET NOCOUNT ON DECLARE @idxTableName SYSNAME, @idxTableID INT, @idxname SYSNAME, @idxid INT, @colCount INT, @IxColumn SYSNAME, @IxFirstColumn BIT, @ColumnIDInTable INT, @ColumnIDInIndex INT, @IsIncludedColumn INT, @sIncludeCols VARCHAR(4000), @sIndexCols VARCHAR(4000), @sSQL VARCHAR(4000), @sParamSQL VARCHAR(4000), @location SYSNAME, @IndexCount INT, @CurrentIndex INT, @CurrentCol INT, @Name VARCHAR(128), @IsPrimaryKey TINYINT, @Fillfactor INT CREATE TABLE #IndexListing ( [IndexListingID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, [TableName] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ObjectID] INT NOT NULL, [IndexName] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [IndexID] INT NOT NULL, [IsPrimaryKey] TINYINT NOT NULL, [FillFactor] INT ) CREATE TABLE #ColumnListing ( [ColumnListingID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, [ColumnIDInTable] INT NOT NULL, [Name] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ColumnIDInIndex] INT NOT NULL, [IsIncludedColumn] BIT NULL ) INSERT INTO #IndexListing( [TableName], [ObjectID], [IndexName], [IndexID], [IsPrimaryKey], [FILLFACTOR] ) SELECT OBJECT_NAME(si.object_id), si.object_id, si.name, si.index_id, si.Is_Primary_Key, si.Fill_Factor FROM sys.indexes si LEFT OUTER JOIN information_schema.table_constraints tc ON si.name = tc.constraint_name AND OBJECT_NAME(si.object_id) = tc.table_name WHERE OBJECTPROPERTY(si.object_id, 'IsUserTable') = 1 ORDER BY OBJECT_NAME(si.object_id), si.index_id SELECT @IndexCount = @@ROWCOUNT, @CurrentIndex = 1 WHILE @CurrentIndex <= @IndexCount BEGIN SELECT @idxTableName = [TableName], @idxTableID = [ObjectID], @idxname = [IndexName], @idxid = [IndexID], @IsPrimaryKey = [IsPrimaryKey], @FillFactor = [FILLFACTOR] FROM #IndexListing WHERE [IndexListingID] = @CurrentIndex -- So - it is either an index or a constraint -- Check if the index is unique IF (@IsPrimaryKey = 1) BEGIN SET @sSQL = 'ALTER TABLE [dbo].[' + @idxTableName + '] ADD CONSTRAINT [' + @idxname + '] PRIMARY KEY ' -- Check if the index is clustered IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsClustered') = 0) BEGIN SET @sSQL = @sSQL + 'NON' END SET @sSQL = @sSQL + 'CLUSTERED' + CHAR(13) + '(' + CHAR(13) END ELSE BEGIN SET @sSQL = 'CREATE ' -- Check if the index is unique IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsUnique') = 1) BEGIN SET @sSQL = @sSQL + 'UNIQUE ' END -- Check if the index is clustered IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsClustered') = 1) BEGIN SET @sSQL = @sSQL + 'CLUSTERED ' END SELECT @sSQL = @sSQL + 'INDEX [' + @idxname + '] ON [dbo].[' + @idxTableName + ']' + CHAR(13) + '(' + CHAR(13), @colCount = 0 END -- Get the number of cols in the index SELECT @colCount = COUNT(*) 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 = @idxtableid AND index_id = @idxid AND ic.is_included_column = 0 -- Get the file group info SELECT @location = f.[name] FROM sys.indexes i INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id] WHERE o.object_id = @idxTableID AND i.index_id = @idxid -- Get all columns of the index INSERT INTO #ColumnListing( [ColumnIDInTable], [Name], [ColumnIDInIndex],[IsIncludedColumn] ) SELECT sc.column_id, sc.name, ic.index_column_id, ic.is_included_column 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 = @idxTableID AND index_id = @idxid ORDER BY ic.index_column_id IF @@ROWCOUNT > 0 BEGIN SELECT @CurrentCol = 1 SELECT @IxFirstColumn = 1, @sIncludeCols = '', @sIndexCols = '' WHILE @CurrentCol <= @ColCount BEGIN SELECT @ColumnIDInTable = ColumnIDInTable, @Name = Name, @ColumnIDInIndex = ColumnIDInIndex, @IsIncludedColumn = IsIncludedColumn FROM #ColumnListing WHERE [ColumnListingID] = @CurrentCol IF @IsIncludedColumn = 0 BEGIN SET @sIndexCols = CHAR(9) + @sIndexCols + '[' + @Name + '] ' -- Check the sort order of the index cols ???????? IF (INDEXKEY_PROPERTY (@idxTableID,@idxid,@ColumnIDInIndex,'IsDescending')) = 0 BEGIN SET @sIndexCols = @sIndexCols + ' ASC ' END ELSE BEGIN SET @sIndexCols = @sIndexCols + ' DESC ' END IF @CurrentCol < @colCount BEGIN SET @sIndexCols = @sIndexCols + ', ' END END ELSE BEGIN -- Check for any include columns IF LEN(@sIncludeCols) > 0 BEGIN SET @sIncludeCols = @sIncludeCols + ',' END SET @sIncludeCols = @sIncludeCols + '[' + @IxColumn + ']' END SET @CurrentCol = @CurrentCol + 1 END TRUNCATE TABLE #ColumnListing --append to the result IF LEN(@sIncludeCols) > 0 SET @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ') ' + ' INCLUDE ( ' + @sIncludeCols + ' ) ' ELSE SET @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ') ' -- Build the options SET @sParamSQL = 'WITH ( PAD_INDEX = ' IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsPadIndex') = 1) SET @sParamSQL = @sParamSQL + 'ON,' ELSE SET @sParamSQL = @sParamSQL + 'OFF,' SET @sParamSQL = @sParamSQL + ' ALLOW_PAGE_LOCKS = ' IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsPageLockDisallowed') = 1) SET @sParamSQL = @sParamSQL + 'ON,' ELSE SET @sParamSQL = @sParamSQL + 'OFF,' SET @sParamSQL = @sParamSQL + ' ALLOW_ROW_LOCKS = ' IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsRowLockDisallowed') = 1) SET @sParamSQL = @sParamSQL + 'ON,' ELSE SET @sParamSQL = @sParamSQL + 'OFF,' SET @sParamSQL = @sParamSQL + ' STATISTICS_NORECOMPUTE = ' IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsStatistics') = 1) SET @sParamSQL = @sParamSQL + 'ON,' ELSE SET @sParamSQL = @sParamSQL + 'OFF,' -- Tweak this - identity = 100% - convert 0 to 100% SET @sParamSQL = @sParamSQL + ' FILLFACTOR = ' + CAST( ISNULL( @FillFactor, 90 ) AS VARCHAR(3) ) + ', ' SET @sParamSQL = @sParamSQL + ' DROP_EXISTING = ON ) ' SET @sIndexCols = @sIndexCols + CHAR(13) + @sParamSQL -- IF THE INDEX IS NOT A PRIMARY KEY - ADD THIS - ELSE DO NOT IF (@IsPrimaryKey = 0) BEGIN SET @sIndexCols = @sIndexCols + ' ON [' + @location + ']' END PRINT @sIndexCols + CHAR(13) END SET @CurrentIndex = @CurrentIndex + 1 ENDEND

RE: Script all Indexes

murali.Jillellamudi — Fri, 06 Aug 2010 06:29:20 GMT

New version is available pls check and let me know

RE: Script all Indexes

andy.cw — Wed, 04 Aug 2010 09:48:19 GMT

Hi,The second SQL script worked for me, brilliant job!Thanks.

RE: Script all Indexes

Douglas Osborne-456728 — Wed, 04 Aug 2010 09:38:37 GMT

Mulali,Also noticed the SQL for the INCLUDE doesn't compile - the [ needs to be a (.Doug

RE: Script all Indexes

Douglas Osborne-456728 — Wed, 04 Aug 2010 09:36:30 GMT

Murali,Just wondering why the INCLUDE data points all have DESC after them?Doug

RE: Script all Indexes

Dan Meenan — Sun, 01 Aug 2010 06:00:28 GMT

Found unicode characters in the script, otherwise was fine. Added FILLFACTOR to the script - important!1. added declaration at the top: declare @fillfactor int2.Added the assignation just above the comment shown here: select @fillfactor = isnull(fill_factor,90) from sys.indexes where object_id = @idxid -- Get the number of cols in the index 3. Then modified the beginning of the "build the options" section: -- Build the options set @sParamSQL = ' WITH (FILLFACTOR = ' + cast(isnull(@fillfactor,90) as varchar(3)) + ', '

RE: Script all Indexes

rVadim — Fri, 30 Jul 2010 14:59:44 GMT

Nice and fast - Thank you.Suggestions: 1. Option to create DROP INDEX statemens. 2. Option to skip clustered indexes.--Vadim.

RE: Script all Indexes

Greg Milner — Fri, 30 Jul 2010 09:32:02 GMT

That one works! Thanks!

RE: Script all Indexes

jamesr-897470 — Fri, 30 Jul 2010 08:13:51 GMT

When I copied the code from the web page and tried to run it in SSMS 2005, I got the same errors. When I tried to save the script to a file, I received a message that there were unicode characters in the file and did I want to save the file with the characters. I said no, closed the file in SSMS and reopened it. I found that the unicode characters where located at the beginning of each line. These showed as ? in the file when I opened it since I did not save them. These did not show as anything but blank space in the original script. I would assume that the same thing caused the errors others are having.

RE: Script all Indexes

murali.Jillellamudi — Fri, 30 Jul 2010 07:57:17 GMT

Can you try the below and let me know : ************************************************************************* DECLARE @idxTableName SYSNAME DECLARE @idxTableID INT DECLARE @idxname SYSNAME DECLARE @idxid INT DECLARE @colCount INT DECLARE @IxColumn SYSNAME DECLARE @IxFirstColumn BIT DECLARE @ColumnIDInTable INT DECLARE @ColumnIDInIndex INT DECLARE @IsIncludedColumn INT DECLARE @sIncludeCols varCHAR(4000) DECLARE @sIndexCols varCHAR(4000) declare @sSQL VARCHAR(4000) declare @rowcnt int declare @sParamSQL VARCHAR(4000) declare @location sysname -- Get all the index info declare curidx cursor for select object_name(si.object_id), si.object_id, si.name, si.index_id from sys.indexes si left join information_schema.table_constraints tc on si.name = tc.constraint_name and object_name(si.object_id) = tc.table_name where objectproperty(si.object_id, 'IsUserTable') = 1 order by object_name(si.object_id), si.index_id open curidx fetch next from curidx into @idxTableName, @idxTableID, @idxname, @idxid --loop while (@@FETCH_STATUS = 0) begin set @sSQL = 'CREATE ' -- Check if the index is unique if (INDEXPROPERTY(@idxTableID, @idxname, 'IsUnique') = 1) set @sSQL = @sSQL + 'UNIQUE ' -- Check if the index is clustered if (INDEXPROPERTY(@idxTableID, @idxname, 'IsClustered') = 1) set @sSQL = @sSQL + 'CLUSTERED ' set @sSQL = @sSQL + 'INDEX ' + @idxname + ' ON ' + @idxTableName + CHAR(13) + '(' set @sSQL = @sSQL + CHAR(13) set @colCount = 0 -- Get the number of cols in the index select @colCount = COUNT(*) from sys.index_columns ic join sys.columns sc on ic.object_id = sc.object_id and ic.column_id = sc.column_id where ic.object_id = @idxtableid and index_id = @idxid and ic.is_included_column = 0 -- Get the file group info select @location = f.[name] from sys.indexes i INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id] Where o.object_id = @idxTableID and i.index_id = @idxid -- Get all columns of the index declare curidxcolumn cursor for select sc.column_id as columnidintable,sc.name,ic.index_column_id columnidinindex,ic.is_included_column as isincludedcolumn from sys.index_columns ic join sys.columns sc on ic.object_id = sc.object_id and ic.column_id = sc.column_id where ic.object_id = @idxTableID and index_id = @idxid order by ic.index_column_id set @IxFirstColumn = 1 set @sIncludeCols = '' set @sIndexCols = '' set @rowcnt = 0 open curidxColumn fetch next from curidxColumn into @ColumnIDInTable, @IxColumn,@ColumnIDInIndex,@IsIncludedColumn --loop while (@@FETCH_STATUS = 0) begin if @IsIncludedColumn = 0 begin set @rowcnt = @rowcnt + 1 set @sIndexCols = char(9) + @sIndexCols + '[' + @IxColumn + ']' -- Check the sort order of the index cols if (INDEXKEY_PROPERTY (@idxTableID,@idxid,@ColumnIDInIndex,'IsDescending')) = 0 set @sIndexCols = @sIndexCols + ' ASC ' else set @sIndexCols = @sIndexCols + ' DESC ' if @rowcnt < @colCount set @sIndexCols = @sIndexCols + ', ' end else begin -- Check for any include columns if len(@sIncludeCols) > 0 set @sIncludeCols = @sIncludeCols + ',' set @sIncludeCols = @sIncludeCols + '[' + @IxColumn + ']' if (INDEXKEY_PROPERTY (@idxTableID,@idxid,@ColumnIDInIndex,'IsDescending')) = 0 set @sIncludeCols = @sIncludeCols + ' ASC ' else set @sIncludeCols = @sIncludeCols + ' DESC ' end fetch next from curidxColumn into @ColumnIDInTable, @IxColumn,@ColumnIDInIndex,@IsIncludedColumn end close curidxColumn deallocate curidxColumn --append to the result if LEN(@sIncludeCols) > 0 set @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ') ' + ' INCLUDE [ ' + @sIncludeCols + ' ] ' else set @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ') ' -- Build the options set @sParamSQL = ' WITH (' if (INDEXPROPERTY(@idxTableID, @idxname, 'IsPadIndex') = 1) set @sParamSQL = @sParamSQL + ' PAD_INDEX = ON, ' else set @sParamSQL = @sParamSQL + ' PAD_INDEX = OFF, ' if (INDEXPROPERTY(@idxTableID, @idxname, 'IsPageLockDisallowed') = 1) set @sParamSQL = @sParamSQL + ' ALLOW_PAGE_LOCKS = ON, ' else set @sParamSQL = @sParamSQL + ' ALLOW_PAGE_LOCKS = OFF, ' if (INDEXPROPERTY(@idxTableID, @idxname, 'IsRowLockDisallowed') = 1) set @sParamSQL = @sParamSQL + ' ALLOW_ROW_LOCKS = ON, ' else set @sParamSQL = @sParamSQL + ' ALLOW_ROW_LOCKS = OFF, ' if (INDEXPROPERTY(@idxTableID, @idxname, 'IsStatistics') = 1) set @sParamSQL = @sParamSQL + ' STATISTICS_NORECOMPUTE = ON, ' else set @sParamSQL = @sParamSQL + ' STATISTICS_NORECOMPUTE = OFF, ' set @sParamSQL = @sParamSQL + ' DROP_EXISTING = ON ) ' set @sIndexCols = @sIndexCols + CHAR(13) + @sParamSQL + ' ON [' + @location + ']' print @sIndexCols fetch next from curidx into @idxTableName, @idxTableID, @idxname, @idxid END close curidx deallocate curidx*************************************************************************CheersMurali

RE: Script all Indexes

ebenraja — Fri, 30 Jul 2010 07:43:14 GMT

Same applies to me, It doesn seem to work well with SQL 2008 SSMS

RE: Script all Indexes

andy.cw — Fri, 30 Jul 2010 06:25:27 GMT

Hi,I'm using SSMS 2008 with SQL2008 backend.Cheers.Here are all the errors I get:-Msg 102, Level 15, State 1, Line 1Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 2Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 3Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 4Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 5Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 6Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 7Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 8Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 9Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 10Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 11Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 12Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 13Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 14Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 15Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 16Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 18Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 20Incorrect syntax near ' '.Msg 137, Level 15, State 2, Line 31Must declare the scalar variable "@idxTableName".Msg 102, Level 15, State 1, Line 35Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 39Incorrect syntax near ' '.Msg 137, Level 15, State 2, Line 40Must declare the scalar variable "@idxTableID".Msg 137, Level 15, State 2, Line 41Must declare the scalar variable "@sSQL".Msg 137, Level 15, State 2, Line 44Must declare the scalar variable "@idxTableID".Msg 137, Level 15, State 2, Line 45Must declare the scalar variable "@sSQL".Msg 137, Level 15, State 2, Line 47Must declare the scalar variable "@sSQL".Msg 137, Level 15, State 2, Line 49Must declare the scalar variable "@sSQL".Msg 102, Level 15, State 1, Line 52Incorrect syntax near ' '.Msg 137, Level 15, State 1, Line 54Must declare the scalar variable "@colCount".Msg 102, Level 15, State 1, Line 71Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 81Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 82Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 83Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 84Incorrect syntax near ' '.Msg 137, Level 15, State 2, Line 87Must declare the scalar variable "@ColumnIDInTable".Msg 102, Level 15, State 1, Line 91Incorrect syntax near ' '.Msg 137, Level 15, State 2, Line 93Must declare the scalar variable "@IsIncludedColumn".Msg 137, Level 15, State 2, Line 96Must declare the scalar variable "@rowcnt".Msg 137, Level 15, State 2, Line 98Must declare the scalar variable "@sIndexCols".Msg 137, Level 15, State 2, Line 101Must declare the scalar variable "@idxTableID".Msg 137, Level 15, State 2, Line 102Must declare the scalar variable "@sIndexCols".Msg 137, Level 15, State 2, Line 104Must declare the scalar variable "@sIndexCols".Msg 137, Level 15, State 2, Line 106Must declare the scalar variable "@rowcnt".Msg 137, Level 15, State 2, Line 107Must declare the scalar variable "@sIndexCols".Msg 137, Level 15, State 2, Line 113Must declare the scalar variable "@sIncludeCols".Msg 137, Level 15, State 2, Line 114Must declare the scalar variable "@sIncludeCols".Msg 137, Level 15, State 2, Line 116Must declare the scalar variable "@sIncludeCols".Msg 137, Level 15, State 2, Line 118Must declare the scalar variable "@idxTableID".Msg 137, Level 15, State 2, Line 119Must declare the scalar variable "@sIncludeCols".Msg 137, Level 15, State 2, Line 121Must declare the scalar variable "@sIncludeCols".Msg 137, Level 15, State 2, Line 125Must declare the scalar variable "@ColumnIDInTable".Msg 137, Level 15, State 2, Line 133Must declare the scalar variable "@sIncludeCols".Msg 137, Level 15, State 2, Line 134Must declare the scalar variable "@sSQL".Msg 137, Level 15, State 2, Line 136Must declare the scalar variable "@sSQL".Msg 102, Level 15, State 1, Line 140Incorrect syntax near ' '.Msg 137, Level 15, State 2, Line 141Must declare the scalar variable "@idxTableID".Msg 137, Level 15, State 2, Line 142Must declare the scalar variable "@sParamSQL".Msg 137, Level 15, State 2, Line 144Must declare the scalar variable "@sParamSQL".Msg 137, Level 15, State 2, Line 146Must declare the scalar variable "@idxTableID".Msg 137, Level 15, State 2, Line 147Must declare the scalar variable "@sParamSQL".Msg 137, Level 15, State 2, Line 149Must declare the scalar variable "@sParamSQL".Msg 137, Level 15, State 2, Line 151Must declare the scalar variable "@idxTableID".Msg 137, Level 15, State 2, Line 152Must declare the scalar variable "@sParamSQL".Msg 137, Level 15, State 2, Line 154Must declare the scalar variable "@sParamSQL".Msg 137, Level 15, State 2, Line 156Must declare the scalar variable "@idxTableID".Msg 137, Level 15, State 2, Line 157Must declare the scalar variable "@sParamSQL".Msg 137, Level 15, State 2, Line 159Must declare the scalar variable "@sParamSQL".Msg 137, Level 15, State 2, Line 161Must declare the scalar variable "@sParamSQL".Msg 137, Level 15, State 2, Line 163Must declare the scalar variable "@sIndexCols".Msg 137, Level 15, State 2, Line 165Must declare the scalar variable "@sIndexCols".Msg 137, Level 15, State 2, Line 167Must declare the scalar variable "@idxTableName".

RE: Script all Indexes

murali.Jillellamudi — Fri, 30 Jul 2010 03:18:43 GMT

Hi Andy , I tried in SSMS for SQL2005, SQL2008 , SQL2008R2 . I could not reproduce your error , It looks like more an environment issue .Do you mind sharing your environment details.ThanksMurali

RE: Script all Indexes

andy.cw — Fri, 30 Jul 2010 03:05:19 GMT

Hi,Tried your script but got a number of Msg 102, Level 15, State 1, Line 1Incorrect syntax near ' '.Any ideas, just wanted to try it out!Cheers.

Script all Indexes

murali.Jillellamudi — Thu, 29 Jul 2010 20:19:23 GMT

Comments posted to this topic are about the item [B]Script all Indexes[/B]