SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2005: Script all Indexes


SQL Server 2005: Script all Indexes

Author
Message
Oxygen
Oxygen
SSC Veteran
SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)

Group: General Forum Members
Points: 289 Visits: 3
Comments posted to this topic are about the item SQL Server 2005: Script all Indexes



Anthony Robinson
Anthony Robinson
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 85
You forgot to declare @PKSQL
neil-560592
neil-560592
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 475
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
philipp.kanne
philipp.kanne
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 11
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
Robert Frasca
Robert Frasca
SSC Eights!
SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)

Group: General Forum Members
Points: 808 Visits: 776
Didn't declare @tablename.

"Beliefs" get in the way of learning.
stephenc-324995
stephenc-324995
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 143
It didn't cater asc or desc order
jeffryfranksjunk
jeffryfranksjunk
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 90
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.
Scott Hannon-278869
Scott Hannon-278869
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 83
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


HARVINDER SINGH-301803
HARVINDER SINGH-301803
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 168
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
Jonathan AC Roberts
Jonathan AC Roberts
SSC Eights!
SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)SSC Eights! (857 reputation)

Group: General Forum Members
Points: 857 Visits: 1905
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search