Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

SQL Server 2005: Script all Indexes Expand / Collapse
Author
Message
Posted Sunday, September 23, 2007 6:57 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 12, 2008 7:59 AM
Points: 275, Visits: 3
Comments posted to this topic are about the item SQL Server 2005: Script all Indexes


Post #401784
Posted Friday, January 04, 2008 11:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 08, 2012 3:57 PM
Points: 6, Visits: 83
You forgot to declare @PKSQL
Post #439074
Posted Friday, January 25, 2008 4:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 10, 2014 3:15 AM
Points: 46, Visits: 417
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
Post #447387
Posted Monday, August 25, 2008 5:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 09, 2010 12:43 AM
Points: 1, 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


Post #558068
Posted Thursday, December 04, 2008 12:58 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 12:23 PM
Points: 545, Visits: 641
Didn't declare @tablename.

"Beliefs" get in the way of learning.
Post #614033
Posted Wednesday, January 21, 2009 1:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 11:35 AM
Points: 1, Visits: 138
It didn't cater asc or desc order
Post #640497
Posted Monday, June 08, 2009 10:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 10:25 AM
Points: 3, Visits: 81
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.
Post #730823
Posted Wednesday, June 17, 2009 7:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 12:22 PM
Points: 3, 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

Post #736508
Posted Wednesday, August 05, 2009 3:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 30, 2013 9:29 PM
Points: 1, Visits: 146
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

Post #765861
Posted Monday, January 18, 2010 8:19 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 14, 2014 5:08 AM
Points: 331, Visits: 1,356
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

Post #849194
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse