September 23, 2007 at 6:57 pm
Comments posted to this topic are about the item SQL Server 2005: Script all Indexes
January 4, 2008 at 11:51 am
You forgot to declare @PKSQL
January 25, 2008 at 4:00 am
The variable @PKSQL is not used, so just comment it out. Then the script works OK.
DECLARE @IXSQL NVARCHAR(4000) SET @PKSQL = ''
to
DECLARE @IXSQL NVARCHAR(4000) --SET @PKSQL = ''
Then it works fine. It saved me quite a lot of time
thanks
http://90.212.51.111 domain
August 25, 2008 at 5:47 am
The "include" index param is missing. I've refactored the script as follows:
-- Get all existing indexes, but NOT the primary keys
DECLARE cIX CURSOR FOR
SELECT OBJECT_NAME(SI.Object_ID),
SI.Object_ID,
SI.Name,
SI.Index_ID,
(select TABLE_SCHEMA from INFORMATION_SCHEMA.TABLES where TABLE_NAME = OBJECT_NAME(SI.Object_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 TC.CONSTRAINT_NAME IS NULL
AND OBJECTPROPERTY(SI.Object_ID, 'IsUserTable') = 1
AND OBJECT_NAME(SI.Object_ID) = @tablename
ORDER BY OBJECT_NAME(SI.Object_ID), SI.Index_ID
DECLARE @IxTable sysname
DECLARE @IxTableID INT
DECLARE @IxName sysname
DECLARE @i_schema sysname
DECLARE @IxID INT
-- Loop through all indexes
OPEN cIX
FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID, @i_schema
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @IXSQL NVARCHAR(4000) //SET @PKSQL = ''
SET @IXSQL = 'CREATE '
-- Check if the index is unique
IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsUnique') = 1)
SET @IXSQL = @IXSQL + 'UNIQUE '
-- Check if the index is clustered
IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)
SET @IXSQL = @IXSQL + 'CLUSTERED '
SET @IXSQL = @IXSQL + 'INDEX [' + @IxName + '] ON [' + @i_schema +'.'+ @IxTable + ']('
-- Get all columns of the index
DECLARE cIxColumn CURSOR FOR
SELECT SC.Name, IC.is_included_column, IC.is_descending_key
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 = @IxTableID AND Index_ID = @IxID
ORDER BY IC.Index_Column_ID
DECLARE @IxColumn sysname
DECLARE @IxFirstColumn BIT SET @IxFirstColumn = 1
declare @i_include bit
declare @i_desc bit
declare @i_sql_col varchar(4000) set @i_sql_col = ''
declare @i_sql_inc varchar(4000) set @i_sql_inc = ''
-- Loop throug all columns of the index and append them to the CREATE statement
OPEN cIxColumn
FETCH NEXT FROM cIxColumn INTO @IxColumn,@i_include,@i_desc
WHILE (@@FETCH_STATUS = 0)
BEGIN
if (@i_include = 1)
set @i_sql_inc = @i_sql_inc +', ' + '[' + @IxColumn + ']'
else
begin
set @i_sql_col = @i_sql_col + ', ' + '[' + @IxColumn + ']'
if (@i_desc = 1)
set @i_sql_col = +@i_sql_col + ' DESC'
end
FETCH NEXT FROM cIxColumn INTO @IxColumn,@i_include,@i_desc
END
CLOSE cIxColumn
DEALLOCATE cIxColumn
-- remove leading ','
if (left(@i_sql_inc,1) = ',')
set @i_sql_inc = right(@i_sql_inc,len(@i_sql_inc)-1)
if (left(@i_sql_col,1) = ',')
set @i_sql_col = right(@i_sql_col,len(@i_sql_col)-1)
SET @IXSQL = @IXSQL + @i_sql_col + ')'
if (@i_sql_inc <> '')
set @IXSQL = @IXSQL + ' INCLUDE (' + @i_sql_inc + ')'
print @IXSQL
FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID, @i_schema
END
December 4, 2008 at 12:58 pm
Didn't declare @tablename.
"Beliefs" get in the way of learning.
January 21, 2009 at 1:54 am
It didn't cater asc or desc order
June 8, 2009 at 10:48 am
you will want to change the order in which columns are added to the script to:
DECLARE cIxColumn CURSOR FOR
SELECT SC.Name
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 = @IxTableID AND Index_ID = @IxID
ORDER BY IC.key_ordinal
key_ordinal is the order in which the columns are built in the index; Index_Column_ID is the column ID, which does not correlate to the structure of the index.
June 17, 2009 at 7:07 am
Modified for INCLUDEd columns and for DESC order, and for long/irregular table and column names.
-- Get all existing indexes, but NOT the primary keys
DECLARE cIX 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 TC.CONSTRAINT_NAME IS NULL
AND OBJECTPROPERTY(SI.Object_ID, 'IsUserTable') = 1
ORDER BY OBJECT_NAME(SI.Object_ID), SI.Index_ID
DECLARE @IxTable SYSNAME
DECLARE @IxTableID INT
DECLARE @IxName SYSNAME
DECLARE @IxID INT
-- Loop through all indexes
OPEN cIX
FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @IXSQL NVARCHAR(4000)
--SET @PKSQL = ''
SET @IXSQL = 'CREATE '
-- Check if the index is unique
IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsUnique') = 1)
SET @IXSQL = @IXSQL + 'UNIQUE '
-- Check if the index is clustered
IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)
SET @IXSQL = @IXSQL + 'CLUSTERED '
SET @IXSQL = @IXSQL + 'INDEX ' + @IxName + ' ON [' + @IxTable + '] ('
-- Get all columns of the index
DECLARE cIxColumn CURSOR FOR
SELECT SC.Name,IC.[is_included_column],IC.is_descending_key
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 = @IxTableID AND Index_ID = @IxID
ORDER BY IC.Index_Column_ID,IC.is_included_column
DECLARE @IxColumn SYSNAME
DECLARE @IxIncl bit
DECLARE @Desc bit
DECLARE @IxIsIncl bit set @IxIsIncl = 0
DECLARE @IxFirstColumn BIT SET @IxFirstColumn = 1
-- Loop throug all columns of the index and append them to the CREATE statement
OPEN cIxColumn
FETCH NEXT FROM cIxColumn 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 @IXSQL = @IXSQL + ') INCLUDE ('
END
ELSE
BEGIN
SET @IXSQL = @IXSQL + ', '
END
END
SET @IXSQL = @IXSQL + '[' + @IxColumn + ']'
--check to see if it's DESC
IF @Desc = 1
SET @IXSQL = @IXSQL + ' DESC'
FETCH NEXT FROM cIxColumn INTO @IxColumn, @IxIncl, @Desc
END
CLOSE cIxColumn
DEALLOCATE cIxColumn
SET @IXSQL = @IXSQL + ')'
-- Print out the CREATE statement for the index
PRINT @IXSQL
FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID
END
CLOSE cIX
DEALLOCATE cIX
August 5, 2009 at 3:25 pm
Missing the filegroup clause so adding it, adding GO clause and modifying the cursor query:
-- Get all existing indexes, but NOT the primary keys
DECLARE cIX CURSOR FOR
SELECT OBJECT_NAME(SI.Object_ID), SI.Object_ID, SI.Name, SI.Index_ID,
FG.name
FROM sys.indexes SI
left outer join sys.filegroups FG
on SI.data_space_id = FG.data_space_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
ORDER BY OBJECT_NAME(SI.Object_ID), SI.Index_ID
DECLARE @IxTable sysname
DECLARE @IxTableID INT
DECLARE @IxName sysname
DECLARE @FGName sysname
DECLARE @IxID INT
-- Loop through all indexes
OPEN cIX
FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID, @FGName
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @IXSQL NVARCHAR(4000)
SET @IXSQL = 'CREATE '
-- Check if the index is unique
IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsUnique') = 1)
SET @IXSQL = @IXSQL + 'UNIQUE '
-- Check if the index is clustered
IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)
SET @IXSQL = @IXSQL + 'CLUSTERED '
SET @IXSQL = @IXSQL + 'INDEX ' + @IxName + ' ON ' + @IxTable + '('
-- Get all columns of the index
DECLARE cIxColumn CURSOR FOR
SELECT SC.Name
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 = @IxTableID AND Index_ID = @IxID
ORDER BY IC.key_ordinal
DECLARE @IxColumn sysname
DECLARE @IxFirstColumn BIT SET @IxFirstColumn = 1
-- Loop throug all columns of the index and append them to the CREATE statement
OPEN cIxColumn
FETCH NEXT FROM cIxColumn INTO @IxColumn
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@IxFirstColumn = 1)
SET @IxFirstColumn = 0
ELSE
SET @IXSQL = @IXSQL + ', '
SET @IXSQL = @IXSQL + @IxColumn
FETCH NEXT FROM cIxColumn INTO @IxColumn
END
CLOSE cIxColumn
DEALLOCATE cIxColumn
SET @IXSQL = @IXSQL + ')' + 'on '+ @FGName
-- Print out the CREATE statement for the index
PRINT @IXSQL
PRINT 'GO'
FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID, @FGName
END
CLOSE cIX
DEALLOCATE cIX
January 18, 2010 at 8:19 am
I've updated the code so that it will include both FileGroups and any INCLUDE columns.
Also made it a stored procedure with the option to include existance test and DROP before create.
--USE myDB
GO
IF NOT EXISTS(SELECT 1
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'INFGenerateIndexesScript'
AND ROUTINE_TYPE = N'PROCEDURE')
BEGIN
EXEC ('CREATE PROCEDURE [dbo].[INFGenerateIndexesScript] AS BEGIN SELECT 1 END')
END
GO
GO
-- **********************************************************************
-- Sample Usage: EXEC INFGenerateIndexesScript 1, 0
-- $Revision: 1.2 $
-- **********************************************************************
ALTER PROCEDURE INFGenerateIndexesScript
(
@IncludeFileGroup bit = 1,
@IncludeDrop bit = 1
)
AS
BEGIN
-- Get all existing indexes, but NOT the primary keys
DECLARE Indexes_cursor CURSOR
FOR SELECT Object_name(SI.Object_Id) TableName,
SI.Object_Id TableId,
SI.[Name] IndexName,
SI.Index_ID IndexId,
FG.[Name] FileGroupName
FROM sys.indexes SI
LEFT JOIN sys.filegroups FG
ON SI.data_space_id = FG.data_space_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
ORDER BY Object_name(SI.Object_Id), SI.Index_ID
DECLARE @TableName sysname
DECLARE @TableId int
DECLARE @IndexName sysname
DECLARE @FileGroupName sysname
DECLARE @IndexId 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 @TableName, @TableId, @IndexName, @IndexId, @FileGroupName
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 [' + @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 [' + @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 @Desc = 1
BEGIN
SET @sCreateSql = @sCreateSql + ' DESC'
END
ELSE
BEGIN
SET @sCreateSql = @sCreateSql + ' ASC'
END
--END IF
FETCH NEXT
FROM IndexColumns_cursor
INTO @IxColumn, @IxIncl, @Desc
END
--END WHILE
CLOSE IndexColumns_cursor
DEALLOCATE IndexColumns_cursor
IF @IncludeFileGroup = 1
BEGIN
SET @sCreateSql = @sCreateSql + @NewLine + ') ON ['+ @FileGroupName + ']' + @NewLine
END
ELSE
BEGIN
SET @sCreateSql = @sCreateSql + @NewLine + ')' + @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 @TableName, @TableId, @IndexName, @IndexId, @FileGroupName
END
--END WHILE
CLOSE Indexes_cursor
DEALLOCATE Indexes_cursor
END
GO
January 29, 2010 at 10:16 am
Hey
I just don’t get it. I run the script in a Database that has tables with indexes but after the script ends no results are showing.
Can anyone help me telling me what I’m doing wrong?
January 29, 2010 at 4:51 pm
forerolui (1/29/2010)
HeyI just don’t get it. I run the script in a Database that has tables with indexes but after the script ends no results are showing.
Can anyone help me telling me what I’m doing wrong?
You need to break the script down to individual commands, to work out where the error is occurring.
http://90.212.51.111 domain
February 1, 2010 at 6:34 am
But I´m not geting error messages, I´m just geting a blank result page.
February 1, 2010 at 6:44 am
forerolui (2/1/2010)
But I´m not geting error messages, I´m just geting a blank result page.
Are you running the script that creates the stored procedure which when run will create the script?
If so you would need to run the stored procedure after you have installed it.
February 1, 2010 at 6:54 am
Thanks for the update. It's proved to be useful many times for me.
"Beliefs" get in the way of learning.
Viewing 15 posts - 1 through 15 (of 46 total)
You must be logged in to reply to this topic. Login to reply