Click here to monitor SSC
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
frobert-982342
frobert-982342
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 7
nvrm my post, i was not using the latest version!

And thx for the nice script!
zashah 83615
zashah 83615
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 75
Very handy script, thanks for your efforts.

Ali
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44768 Visits: 39845
Caveate Emptor. The latest listed script misses unique (clustered or not) and clustered indexes.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jonathan AC Roberts
Jonathan AC Roberts
SSC-Addicted
SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)

Group: General Forum Members
Points: 430 Visits: 1836
Jeff,
The following lines in the SP exclude the primary key and any indexes that are created via adding a unique constraint rather than adding a unique index.
AND SI.is_primary_key = 0
AND SI.is_unique_constraint = 0
Resender
Resender
SSC-Addicted
SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)

Group: General Forum Members
Points: 473 Visits: 1564
Does this work and how
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44768 Visits: 39845
Jonathan AC Roberts (1/22/2013)
Jeff,
The following lines in the SP exclude the primary key and any indexes that are created via adding a unique constraint rather than adding a unique index.
AND SI.is_primary_key = 0
AND SI.is_unique_constraint = 0


Sorry for the late feedback. Yes, I agree. I just can't imagine why anyone would do such a thing in a script titled "Script ALL Indexes".

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jonathan AC Roberts
Jonathan AC Roberts
SSC-Addicted
SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)

Group: General Forum Members
Points: 430 Visits: 1836
Jeff Moden (2/8/2013)

Sorry for the late feedback. Yes, I agree. I just can't imagine why anyone would do such a thing in a script titled "Script ALL Indexes".

Jeff, Yes a valid point, I've amended the procedure so it takes a table-name-pattern parameter so you can enter a table name with wildcards to generate index script for the only tables you want.

IF NOT EXISTS(SELECT NULL
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'utils'
AND SCHEMA_OWNER = 'dbo')
BEGIN
EXEC('CREATE SCHEMA utils AUTHORIZATION dbo')
END
GO

IF NOT EXISTS(SELECT NULL
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'GenerateIndexesScript'
AND ROUTINE_TYPE = N'PROCEDURE')
BEGIN
EXEC ('CREATE PROCEDURE [utils].[GenerateIndexesScript] AS BEGIN SELECT 1 END')
END
GO

/*
Adapted from http://www.sqlservercentral.com/Forums/Topic401784-562-2.aspx (by Jonathan AC Roberts. )

Modifications 10/06/2010 By R.Doering - http://sqlsolace.blogspot.com

1) Changed Schema of routine to Utils
2) Changed Name from INFGenerateIndexesScript to GenerateIndexesScript
3) Added Schemas to script
4) Reformatted for clarity

-- Usage: EXEC utils.GenerateIndexesScript '%O%', 1, 0, 0
Sample call utils.GenerateIndexesScript

Modifications 2012-May-04 R. Gosling
1) Added in the Schema name to table name

*/
ALTER PROCEDURE utils.GenerateIndexesScript
(
@TableNamePattern sysname = '%',
@IncludeFileGroup bit = 1,
@IncludeDrop bit = 1,
@IncludeFillFactor bit = 1
)
AS

BEGIN
-- Get all existing indexes, but NOT the primary keys
DECLARE Indexes_cursor cursor
FOR SELECT SC.Name AS SchemaName,
SO.Name AS TableName,
SI.OBJECT_ID AS TableId,
SI.[Name] AS IndexName,
SI.Index_ID AS IndexId,
FG.[Name] AS FileGroupName,
CASE WHEN SI.Fill_Factor = 0 THEN 100 ELSE SI.Fill_Factor END Fill_Factor
FROM sys.indexes SI
LEFT JOIN sys.filegroups FG
ON SI.data_space_id = FG.data_space_id
INNER JOIN sys.objects SO
ON SI.OBJECT_ID = SO.OBJECT_ID
INNER JOIN sys.schemas SC
ON SC.schema_id = SO.schema_id
WHERE OBJECTPROPERTY(SI.OBJECT_ID, 'IsUserTable') = 1
AND SI.[Name] IS NOT NULL
AND SI.is_primary_key = 0
AND SI.is_unique_constraint = 0
AND INDEXPROPERTY(SI.OBJECT_ID, SI.[Name], 'IsStatistics') = 0
AND OBJECT_NAME(SI.OBJECT_ID) LIKE @TableNamePattern
ORDER BY OBJECT_NAME(SI.OBJECT_ID), SI.Index_ID

DECLARE @SchemaName sysname
DECLARE @TableName sysname
DECLARE @TableId int
DECLARE @IndexName sysname
DECLARE @FileGroupName sysname
DECLARE @IndexId int
DECLARE @FillFactor int

DECLARE @NewLine nvarchar(4000)
SET @NewLine = char(13) + char(10)
DECLARE @Tab nvarchar(4000)
SET @Tab = SPACE(4)

-- Loop through all indexes
OPEN Indexes_cursor

FETCH NEXT
FROM Indexes_cursor
INTO @SchemaName, @TableName, @TableId, @IndexName, @IndexId, @FileGroupName, @FillFactor

WHILE (@@FETCH_STATUS = 0)
BEGIN

DECLARE @sIndexDesc nvarchar(4000)
DECLARE @sCreateSql nvarchar(4000)
DECLARE @sDropSql nvarchar(4000)

SET @sIndexDesc = '-- Index ' + @IndexName + ' on table ' + @TableName
SET @sDropSql = 'IF EXISTS(SELECT 1' + @NewLine
+ ' FROM sysindexes si' + @NewLine
+ ' INNER JOIN sysobjects so' + @NewLine
+ ' ON so.id = si.id' + @NewLine
+ ' WHERE si.[Name] = N''' + @IndexName + ''' -- Index Name' + @NewLine
+ ' AND so.[Name] = N''' + @TableName + ''') -- Table Name' + @NewLine
+ 'BEGIN' + @NewLine
+ ' DROP INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + ']' + @NewLine
+ 'END' + @NewLine

SET @sCreateSql = 'CREATE '

-- Check if the index is unique
IF (IndexProperty(@TableId, @IndexName, 'IsUnique') = 1)
BEGIN
SET @sCreateSql = @sCreateSql + 'UNIQUE '
END
--END IF
-- Check if the index is clustered
IF (IndexProperty(@TableId, @IndexName, 'IsClustered') = 1)
BEGIN
SET @sCreateSql = @sCreateSql + 'CLUSTERED '
END
--END IF

SET @sCreateSql = @sCreateSql + 'INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + ']' + @NewLine + '(' + @NewLine

-- Get all columns of the index
DECLARE IndexColumns_cursor CURSOR
FOR SELECT SC.[Name],
IC.[is_included_column],
IC.is_descending_key
FROM sys.index_columns IC
INNER JOIN sys.columns SC
ON IC.OBJECT_ID = SC.OBJECT_ID
AND IC.Column_ID = SC.Column_ID
WHERE IC.OBJECT_ID = @TableId
AND Index_ID = @IndexId
ORDER BY IC.[is_included_column],
IC.key_ordinal

DECLARE @IxColumn sysname
DECLARE @IxIncl bit
DECLARE @Desc bit
DECLARE @IxIsIncl bit
SET @IxIsIncl = 0
DECLARE @IxFirstColumn bit
SET @IxFirstColumn = 1

-- Loop through all columns of the index and append them to the CREATE statement
OPEN IndexColumns_cursor
FETCH NEXT
FROM IndexColumns_cursor
INTO @IxColumn, @IxIncl, @Desc

WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@IxFirstColumn = 1)
BEGIN
SET @IxFirstColumn = 0
END
ELSE
BEGIN
--check to see if it's an included column
IF (@IxIsIncl = 0) AND (@IxIncl = 1)
BEGIN
SET @IxIsIncl = 1
SET @sCreateSql = @sCreateSql + @NewLine + ')' + @NewLine + 'INCLUDE' + @NewLine + '(' + @NewLine
END
ELSE
BEGIN
SET @sCreateSql = @sCreateSql + ',' + @NewLine
END
--END IF
END
--END IF

SET @sCreateSql = @sCreateSql + @Tab + '[' + @IxColumn + ']'
-- check if ASC or DESC
IF @IxIsIncl = 0
BEGIN
IF @Desc = 1
BEGIN
SET @sCreateSql = @sCreateSql + ' DESC'
END
ELSE
BEGIN
SET @sCreateSql = @sCreateSql + ' ASC'
END
--END IF
END
--END IF
FETCH NEXT
FROM IndexColumns_cursor
INTO @IxColumn, @IxIncl, @Desc
END
--END WHILE
CLOSE IndexColumns_cursor
DEALLOCATE IndexColumns_cursor

SET @sCreateSql = @sCreateSql + @NewLine + ') '

IF @IncludeFillFactor = 1
BEGIN
SET @sCreateSql = @sCreateSql + @NewLine + 'WITH (FillFactor = ' + CAST(@FillFactor AS varchar(13)) + ')' + @NewLine
END
--END IF

IF @IncludeFileGroup = 1
BEGIN
SET @sCreateSql = @sCreateSql + 'ON ['+ @FileGroupName + ']' + @NewLine
END
ELSE
BEGIN
SET @sCreateSql = @sCreateSql + @NewLine
END
--END IF

PRINT '-- **********************************************************************'
PRINT @sIndexDesc
PRINT '-- **********************************************************************'

IF @IncludeDrop = 1
BEGIN
PRINT @sDropSql
PRINT 'GO'
END
--END IF

PRINT @sCreateSql
PRINT 'GO' + @NewLine + @NewLine

FETCH NEXT
FROM Indexes_cursor
INTO @SchemaName, @TableName, @TableId, @IndexName, @IndexId, @FileGroupName, @FillFactor
END
--END WHILE
CLOSE Indexes_cursor
DEALLOCATE Indexes_cursor

END
GO


philipp.ketelhut
philipp.ketelhut
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 0
Hy foks,
found you script just a few min ago.
I added some suff to it!
Now available @InclucdeCheck as well as @InclucdeTryCatch :-D

@IncludeCheck works only if @IncludeDrop is equal to 0! Since the drop makes a check unnecessarily!

ALTER PROCEDURE utils.GenerateIndexesScript
(
@TableNamePattern sysname = '%',
@IncludeFileGroup bit = 1,
@IncludeDrop bit = 1,
@IncludeFillFactor bit = 0,
   @InclucdeCheck bit = 1,
   @InclucdeTryCatch bit = 1
)
AS

BEGIN
-- Get all existing indexes, but NOT the primary keys
DECLARE Indexes_cursor cursor
FOR SELECT SC.Name AS SchemaName,
SO.Name AS TableName,
SI.OBJECT_ID AS TableId,
SI.[Name] AS IndexName,
SI.Index_ID AS IndexId,
FG.[Name] AS FileGroupName,
CASE WHEN SI.Fill_Factor = 0 THEN 100 ELSE SI.Fill_Factor END Fill_Factor
FROM sys.indexes SI
LEFT JOIN sys.filegroups FG
ON SI.data_space_id = FG.data_space_id
INNER JOIN sys.objects SO
ON SI.OBJECT_ID = SO.OBJECT_ID
INNER JOIN sys.schemas SC
ON SC.schema_id = SO.schema_id
WHERE OBJECTPROPERTY(SI.OBJECT_ID, 'IsUserTable') = 1
AND SI.[Name] IS NOT NULL
AND SI.is_primary_key = 0
AND SI.is_unique_constraint = 0
AND INDEXPROPERTY(SI.OBJECT_ID, SI.[Name], 'IsStatistics') = 0
AND OBJECT_NAME(SI.OBJECT_ID) LIKE @TableNamePattern
ORDER BY OBJECT_NAME(SI.OBJECT_ID), SI.Index_ID

DECLARE @SchemaName sysname
DECLARE @TableName sysname
DECLARE @TableId int
DECLARE @IndexName sysname
DECLARE @FileGroupName sysname
DECLARE @IndexId int
DECLARE @FillFactor int

DECLARE @NewLine nvarchar(4000)
SET @NewLine = char(13) + char(10)
DECLARE @Tab nvarchar(4000)
SET @Tab = SPACE(4)

-- Loop through all indexes
OPEN Indexes_cursor

FETCH NEXT
FROM Indexes_cursor
INTO @SchemaName, @TableName, @TableId, @IndexName, @IndexId, @FileGroupName, @FillFactor

WHILE (@@FETCH_STATUS = 0)
BEGIN

DECLARE @sIndexDesc nvarchar(4000)
DECLARE @sCreateSql nvarchar(4000)
DECLARE @sDropSql nvarchar(4000)

SET @sIndexDesc = '-- Index ' + @IndexName + ' on table ' + @TableName
SET @sDropSql = 'IF EXISTS(SELECT 1' + @NewLine
+ ' FROM sysindexes si' + @NewLine
+ ' INNER JOIN sysobjects so' + @NewLine
+ ' ON so.id = si.id' + @NewLine
+ ' WHERE si.[Name] = N''' + @IndexName + ''' -- Index Name' + @NewLine
+ ' AND so.[Name] = N''' + @TableName + ''') -- Table Name' + @NewLine
+ 'BEGIN' + @NewLine
+ ' DROP INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + ']' + @NewLine
+ 'END' + @NewLine

SET @sCreateSql = 'CREATE '

-- Check if the index is unique
IF (IndexProperty(@TableId, @IndexName, 'IsUnique') = 1)
BEGIN
SET @sCreateSql = @sCreateSql + 'UNIQUE '
END
--END IF
-- Check if the index is clustered
IF (IndexProperty(@TableId, @IndexName, 'IsClustered') = 1)
BEGIN
SET @sCreateSql = @sCreateSql + 'CLUSTERED '
END
--END IF

SET @sCreateSql = @sCreateSql + 'INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + ']' + @NewLine + '(' + @NewLine

-- Get all columns of the index
DECLARE IndexColumns_cursor CURSOR
FOR SELECT SC.[Name],
IC.[is_included_column],
IC.is_descending_key
FROM sys.index_columns IC
INNER JOIN sys.columns SC
ON IC.OBJECT_ID = SC.OBJECT_ID
AND IC.Column_ID = SC.Column_ID
WHERE IC.OBJECT_ID = @TableId
AND Index_ID = @IndexId
ORDER BY IC.[is_included_column],
IC.key_ordinal

DECLARE @IxColumn sysname
DECLARE @IxIncl bit
DECLARE @Desc bit
DECLARE @IxIsIncl bit
SET @IxIsIncl = 0
DECLARE @IxFirstColumn bit
SET @IxFirstColumn = 1

-- Loop through all columns of the index and append them to the CREATE statement
OPEN IndexColumns_cursor
FETCH NEXT
FROM IndexColumns_cursor
INTO @IxColumn, @IxIncl, @Desc

WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@IxFirstColumn = 1)
BEGIN
SET @IxFirstColumn = 0
END
ELSE
BEGIN
--check to see if it's an included column
IF (@IxIsIncl = 0) AND (@IxIncl = 1)
BEGIN
SET @IxIsIncl = 1
SET @sCreateSql = @sCreateSql + @NewLine + ')' + @NewLine + 'INCLUDE' + @NewLine + '(' + @NewLine
END
ELSE
BEGIN
SET @sCreateSql = @sCreateSql + ',' + @NewLine
END
--END IF
END
--END IF

SET @sCreateSql = @sCreateSql + @Tab + '[' + @IxColumn + ']'
-- check if ASC or DESC
IF @IxIsIncl = 0
BEGIN
IF @Desc = 1
BEGIN
SET @sCreateSql = @sCreateSql + ' DESC'
END
ELSE
BEGIN
SET @sCreateSql = @sCreateSql + ' ASC'
END
--END IF
END
--END IF
FETCH NEXT
FROM IndexColumns_cursor
INTO @IxColumn, @IxIncl, @Desc
END
--END WHILE
CLOSE IndexColumns_cursor
DEALLOCATE IndexColumns_cursor

SET @sCreateSql = @sCreateSql + @NewLine + ') '

IF @IncludeFillFactor = 1
BEGIN
SET @sCreateSql = @sCreateSql + @NewLine + 'WITH (FillFactor = ' + CAST(@FillFactor AS varchar(13)) + ')' + @NewLine
END
--END IF

IF @IncludeFileGroup = 1
BEGIN
SET @sCreateSql = @sCreateSql + 'ON ['+ @FileGroupName + ']' + @NewLine
END
ELSE
BEGIN
SET @sCreateSql = @sCreateSql + @NewLine
END
--END IF

PRINT '-- **********************************************************************'
PRINT @sIndexDesc
PRINT '-- **********************************************************************'

IF @IncludeDrop = 1
BEGIN
PRINT @sDropSql
PRINT 'GO'
END
--END IF
         --- Insert by PK 31.05.2013
         If @IncludeDrop=0 and @InclucdeCheck=1
            Begin
               Print'IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name = ''' +@IndexName+''' AND object_id = OBJECT_ID('''+@TableName+'''))'
               Print'Begin'
            End
         if @InclucdeTryCatch=1
            Begin
               Print 'Begin Try'
            end
PRINT @sCreateSql
         --- Insert by PK 31.05.2013
         if @InclucdeTryCatch=1
         Begin
            Print 'End Try'
            Print 'Begin Catch'
            Print 'RAISERROR (''The Index ' + @IndexName + ' on Table '+@TableName+' could not be created'', 11,1)'
            Print 'End Catch'
         End
         If @IncludeDrop=0 and @InclucdeCheck=1
            Begin
               Print'End'
            End
PRINT 'GO' + @NewLine + @NewLine

FETCH NEXT
FROM Indexes_cursor
INTO @SchemaName, @TableName, @TableId, @IndexName, @IndexId, @FileGroupName, @FillFactor
END
--END WHILE
CLOSE Indexes_cursor
DEALLOCATE Indexes_cursor

END
GO



philipp.ketelhut
philipp.ketelhut
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 0
For all how love copy paste and dont know what to do, here is a skript that works without calling a stored procedure

BEGIN
Declare @TableNamePattern sysname = '%';
Declare @IncludeFileGroup bit = 1;
Declare @IncludeDrop bit = 1;
Declare @IncludeFillFactor bit = 0;
Declare   @InclucdeCheck bit = 1;
Declare   @InclucdeTryCatch bit = 1;
-- Get all existing indexes, but NOT the primary keys
DECLARE Indexes_cursor cursor
FOR SELECT SC.Name AS SchemaName,
SO.Name AS TableName,
SI.OBJECT_ID AS TableId,
SI.[Name] AS IndexName,
SI.Index_ID AS IndexId,
FG.[Name] AS FileGroupName,
CASE WHEN SI.Fill_Factor = 0 THEN 100 ELSE SI.Fill_Factor END Fill_Factor
FROM sys.indexes SI
LEFT JOIN sys.filegroups FG
ON SI.data_space_id = FG.data_space_id
INNER JOIN sys.objects SO
ON SI.OBJECT_ID = SO.OBJECT_ID
INNER JOIN sys.schemas SC
ON SC.schema_id = SO.schema_id
WHERE OBJECTPROPERTY(SI.OBJECT_ID, 'IsUserTable') = 1
AND SI.[Name] IS NOT NULL
AND SI.is_primary_key = 0
AND SI.is_unique_constraint = 0
AND INDEXPROPERTY(SI.OBJECT_ID, SI.[Name], 'IsStatistics') = 0
AND OBJECT_NAME(SI.OBJECT_ID) LIKE @TableNamePattern
ORDER BY OBJECT_NAME(SI.OBJECT_ID), SI.Index_ID

DECLARE @SchemaName sysname
DECLARE @TableName sysname
DECLARE @TableId int
DECLARE @IndexName sysname
DECLARE @FileGroupName sysname
DECLARE @IndexId int
DECLARE @FillFactor int

DECLARE @NewLine nvarchar(4000)
SET @NewLine = char(13) + char(10)
DECLARE @Tab nvarchar(4000)
SET @Tab = SPACE(4)

-- Loop through all indexes
OPEN Indexes_cursor

FETCH NEXT
FROM Indexes_cursor
INTO @SchemaName, @TableName, @TableId, @IndexName, @IndexId, @FileGroupName, @FillFactor

WHILE (@@FETCH_STATUS = 0)
BEGIN

DECLARE @sIndexDesc nvarchar(4000)
DECLARE @sCreateSql nvarchar(4000)
DECLARE @sDropSql nvarchar(4000)

SET @sIndexDesc = '-- Index ' + @IndexName + ' on table ' + @TableName
SET @sDropSql = 'IF EXISTS(SELECT 1' + @NewLine
+ ' FROM sysindexes si' + @NewLine
+ ' INNER JOIN sysobjects so' + @NewLine
+ ' ON so.id = si.id' + @NewLine
+ ' WHERE si.[Name] = N''' + @IndexName + ''' -- Index Name' + @NewLine
+ ' AND so.[Name] = N''' + @TableName + ''') -- Table Name' + @NewLine
+ 'BEGIN' + @NewLine
+ ' DROP INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + ']' + @NewLine
+ 'END' + @NewLine

SET @sCreateSql = 'CREATE '

-- Check if the index is unique
IF (IndexProperty(@TableId, @IndexName, 'IsUnique') = 1)
BEGIN
SET @sCreateSql = @sCreateSql + 'UNIQUE '
END
--END IF
-- Check if the index is clustered
IF (IndexProperty(@TableId, @IndexName, 'IsClustered') = 1)
BEGIN
SET @sCreateSql = @sCreateSql + 'CLUSTERED '
END
--END IF

SET @sCreateSql = @sCreateSql + 'INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + ']' + @NewLine + '(' + @NewLine

-- Get all columns of the index
DECLARE IndexColumns_cursor CURSOR
FOR SELECT SC.[Name],
IC.[is_included_column],
IC.is_descending_key
FROM sys.index_columns IC
INNER JOIN sys.columns SC
ON IC.OBJECT_ID = SC.OBJECT_ID
AND IC.Column_ID = SC.Column_ID
WHERE IC.OBJECT_ID = @TableId
AND Index_ID = @IndexId
ORDER BY IC.[is_included_column],
IC.key_ordinal

DECLARE @IxColumn sysname
DECLARE @IxIncl bit
DECLARE @Desc bit
DECLARE @IxIsIncl bit
SET @IxIsIncl = 0
DECLARE @IxFirstColumn bit
SET @IxFirstColumn = 1

-- Loop through all columns of the index and append them to the CREATE statement
OPEN IndexColumns_cursor
FETCH NEXT
FROM IndexColumns_cursor
INTO @IxColumn, @IxIncl, @Desc

WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@IxFirstColumn = 1)
BEGIN
SET @IxFirstColumn = 0
END
ELSE
BEGIN
--check to see if it's an included column
IF (@IxIsIncl = 0) AND (@IxIncl = 1)
BEGIN
SET @IxIsIncl = 1
SET @sCreateSql = @sCreateSql + @NewLine + ')' + @NewLine + 'INCLUDE' + @NewLine + '(' + @NewLine
END
ELSE
BEGIN
SET @sCreateSql = @sCreateSql + ',' + @NewLine
END
--END IF
END
--END IF

SET @sCreateSql = @sCreateSql + @Tab + '[' + @IxColumn + ']'
-- check if ASC or DESC
IF @IxIsIncl = 0
BEGIN
IF @Desc = 1
BEGIN
SET @sCreateSql = @sCreateSql + ' DESC'
END
ELSE
BEGIN
SET @sCreateSql = @sCreateSql + ' ASC'
END
--END IF
END
--END IF
FETCH NEXT
FROM IndexColumns_cursor
INTO @IxColumn, @IxIncl, @Desc
END
--END WHILE
CLOSE IndexColumns_cursor
DEALLOCATE IndexColumns_cursor

SET @sCreateSql = @sCreateSql + @NewLine + ') '

IF @IncludeFillFactor = 1
BEGIN
SET @sCreateSql = @sCreateSql + @NewLine + 'WITH (FillFactor = ' + CAST(@FillFactor AS varchar(13)) + ')' + @NewLine
END
--END IF

IF @IncludeFileGroup = 1
BEGIN
SET @sCreateSql = @sCreateSql + 'ON ['+ @FileGroupName + ']' + @NewLine
END
ELSE
BEGIN
SET @sCreateSql = @sCreateSql + @NewLine
END
--END IF

PRINT '-- **********************************************************************'
PRINT @sIndexDesc
PRINT '-- **********************************************************************'

IF @IncludeDrop = 1
BEGIN
PRINT @sDropSql
PRINT 'GO'
END
--END IF
         --- Insert by PK 31.05.2013
         If @IncludeDrop=0 and @InclucdeCheck=1
            Begin
               Print'IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name = ''' +@IndexName+''' AND object_id = OBJECT_ID('''+@TableName+'''))'
               Print'Begin'
            End
         if @InclucdeTryCatch=1
            Begin
               Print 'Begin Try'
            end
PRINT @sCreateSql
         --- Insert by PK 31.05.2013
         if @InclucdeTryCatch=1
         Begin
            Print 'End Try'
            Print 'Begin Catch'
            Print 'RAISERROR (''The Index ' + @IndexName + ' on Table '+@TableName+' could not be created'', 11,1)'
            Print 'End Catch'
         End
         If @IncludeDrop=0 and @InclucdeCheck=1
            Begin
               Print'End'
            End
PRINT 'GO' + @NewLine + @NewLine

FETCH NEXT
FROM Indexes_cursor
INTO @SchemaName, @TableName, @TableId, @IndexName, @IndexId, @FileGroupName, @FillFactor
END
--END WHILE
CLOSE Indexes_cursor
DEALLOCATE Indexes_cursor

END


niquem1974
niquem1974
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 323
....And the ball keeps rolling

I found this solution and tinkered with it for my own needs

I have a Utils DB that I call SPs from therefore I need to be able to execute from another Db and be able to add the indicies to another Db or linked server .
This script will run on a single table at a time (as I plan to call this as part of an overarching process that may or may not need to create Indices)

Essentially I have added the ability to:

Execute the processes via setting @Exe param
Execute and get the statements to run on another Db/Server via the @ToStr param

I have removed the ability to:
Run on all tables in the given DB

Hope that helps someone out of a hole
Enjoy

** I apologise that the formatting appears to have gone a bit squiffy on copy & paste....I cannae be arsed to go through and sort it out but Im sure you all get picture Smile



CREATE PROCEDURE sp_util_GenerateIndexesScript(@FrmStr varchar(316), @ToStr varchar(316) = NULL, @Exe BIT = NULL,

@IncludeFileGroup   bit = 1,
@IncludeDrop   bit = 1,
@IncludeFillFactor   bit = 0,
@IncludeCheck   bit = 1,
@IncludeTryCatch   bit = 0
)
AS
/*
This Sp will allow you to script the indices from a table for recreation

@FromTbl   = Can be a fully qualified extended name as you can run into a different DB on a differing server
@ToTbl   = Can be left blank or you can pass a partially or fully qualified name
@Exe      = Can be left blank or marked as 1. This will mean that the script executes rather than just outputs a script

Examples:
EXEC sp_util_GenerateIndexesScript 'Db1.dbo.TblData_Emails', 'LinkedServ1.Db2.dbo.TblData_Emails' - This will create the index from the first local table on the remote server

*/
DECLARE
@CurTbl      varchar(max),
@RunSql      varchar(max),
@ICSql      nvarchar(max),
@UseServ    varchar(200),
@UseServCls varchar(2),
@FrmServ    varchar(30),
@FrmDb      varchar(128),
@FrmSch      varchar(30),
@FrmTbl      varchar(128),
@ToServ      varchar(30),
@ToDb      varchar(128),
@ToSch      varchar(30),
@ToTbl      varchar(128)
/*   
--Test settings
   ,@FrmStr      varchar(316),
   @ToStr      varchar(316),
   @Exe         bit = 1,
@IncludeFileGroup   bit = 1,
@IncludeDrop      bit = 1,
@IncludeFillFactor   bit = 0,
@IncludeCheck   bit = 1,
@IncludeTryCatch   bit = 0
Set @FrmStr = 'DB1.dbo.TblData_Emails'
Set @ToStr = --'Server01.DB2.dbo.TblData_Emails'
'DB3.dbo.TblData_Emails'
*/
   
Set @FrmServ   = CASE WHEN PARSENAME(@FrmStr,4)IS NULL THEN ''      ELSE PARSENAME(@FrmStr,4)+'.' END
Set @FrmDB   = CASE WHEN PARSENAME(@FrmStr,3)IS NULL THEN ''      ELSE PARSENAME(@FrmStr,3)+'.' END
Set @FrmSch   = CASE WHEN PARSENAME(@FrmStr,2)IS NULL THEN ''      ELSE PARSENAME(@FrmStr,2)+'.' END
Set @FrmTbl   = PARSENAME(@FrmStr,1)

Set @ToServ   = CASE WHEN PARSENAME(@ToStr,4)IS NULL THEN @FrmServ   ELSE PARSENAME(@ToStr,4)+'.' END
Set @ToDB      = CASE WHEN PARSENAME(@ToStr,3)IS NULL THEN @FrmDb      ELSE PARSENAME(@ToStr,3)+'.' END
Set @ToSch   = CASE WHEN PARSENAME(@ToStr,2)IS NULL THEN @FrmSch      ELSE PARSENAME(@ToStr,2)+'.' END
Set @ToTbl      = CASE WHEN PARSENAME(@ToStr,1)IS NULL THEN @FrmTbl      ELSE PARSENAME(@ToStr,1)    END

Set @Exe      = CASE WHEN @Exe <> 1               THEN 0 ELSE 1 END
Set @RunSql   = ''
Set @UseServ   = 'EXECUTE ' + @ToServ + REPLACE(@ToDb, '.','')+'.[dbo].[sp_executesql] N'''

BEGIN
-- Get all existing indexes, but NOT the primary keys
Set @CurTbl =
            '
             DECLARE Indexes_cursor cursor
               FOR
                  SELECT SC.Name AS SchemaName,
                        SO.Name AS TableName,
                        SI.OBJECT_ID AS TableId,
                        SI.[Name] AS IndexName,
                        SI.Index_ID AS IndexId,
                        FG.[Name] AS FileGroupName,
                        CASE WHEN SI.Fill_Factor = 0 THEN 100 ELSE SI.Fill_Factor END as Fill_Factor
                        FROM '      + @FrmServ + @FrmDb + 'sys.indexes AS SI
                        LEFT JOIN ' + @FrmServ + @FrmDb + 'sys.filegroups AS FG
                  ON SI.data_space_id = FG.data_space_id
                  INNER JOIN ' + @FrmServ + @FrmDb + 'sys.objects   AS SO
                  ON SI.OBJECT_ID = SO.OBJECT_ID
                  INNER JOIN ' + @FrmServ + @FrmDb + 'sys.schemas   AS SC
                  ON SC.schema_id = SO.schema_id
                  WHERE SO.Type = ''U''
                   AND SI.[Name] IS NOT NULL
                   AND SI.is_primary_key = 0
                   AND SI.is_unique_constraint = 0
                   AND SI.is_disabled = 0
                   --AND INDEXPROPERTY(SI.OBJECT_ID, SI.[Name], ''IsStatistics'') = 0
                   AND SO.Name =''' + @FrmTbl + '''
                   ORDER BY OBJECT_NAME(SI.OBJECT_ID), SI.Index_ID
            '
Exec (@CurTbl)

DECLARE @SchemaName sysname
DECLARE @TableName sysname
DECLARE @TableId int
DECLARE @IndexName sysname
DECLARE @FileGroupName sysname
DECLARE @IndexId int
DECLARE @FillFactor int
DECLARE @NewLine      nvarchar(4000)
DECLARE @Tab         nvarchar(4000)

SET @NewLine = char(13) + char(10)
SET @Tab = SPACE(4)

-- Loop through all indexes
OPEN Indexes_cursor

FETCH NEXT
FROM Indexes_cursor
INTO @SchemaName, @TableName, @TableId, @IndexName, @IndexId, @FileGroupName, @FillFactor

WHILE (@@FETCH_STATUS = 0)
BEGIN

DECLARE @sIndexDesc nvarchar(4000)
DECLARE @sCreateSql nvarchar(4000)
DECLARE @sDropSql nvarchar(4000)

SET @sIndexDesc = '-- Create Index ' + @IndexName + ' on table ' + @ToServ + @ToDb + '[' + @SchemaName + '].[' + @TableName + ']'
SET @sDropSql =
                     'IF EXISTS(SELECT 1'   + @NewLine
+ ' FROM '      + @ToDb + '.sysindexes si' + @NewLine
+ ' INNER JOIN ' + @ToDb + '.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 ' + @ToDb + '[' + @SchemaName + '].[' + @TableName + ']' + @NewLine
+ 'END'
         
         SET @RunSql      = ''
SET @sCreateSql = 'CREATE '

-- Check if the index is unique
IF (IndexProperty(@TableId, @IndexName, 'IsUnique') = 1)
BEGIN
SET @sCreateSql = @sCreateSql + 'UNIQUE '
END

-- Check if the index is clustered
IF (IndexProperty(@TableId, @IndexName, 'IsClustered') = 1)
BEGIN
SET @sCreateSql = @sCreateSql + 'CLUSTERED '
END

SET @sCreateSql = @sCreateSql + 'INDEX [' + @IndexName + '] ON ' + @ToDB + '[' + @SchemaName + '].[' + @TableName + ']' + @NewLine + '(' + @NewLine

-- Get all columns of the index
Set @ICSql =
'
DECLARE IndexColumns_cursor CURSOR
FOR SELECT SC.[Name],
IC.[is_included_column],
IC.is_descending_key
FROM '    + @FrmServ + @FrmDb + 'sys.index_columns as IC
INNER JOIN '+ @FrmServ + @FrmDb + 'sys.columns as SC
ON IC.OBJECT_ID = SC.OBJECT_ID
AND IC.Column_ID = SC.Column_ID
WHERE IC.OBJECT_ID = @TId
AND Index_ID = @IId
ORDER BY IC.[is_included_column],
IC.key_ordinal
'
Exec Sp_ExecuteSQL @ICSql, N'@TId as integer, @IId as integer', @TId = @TableId, @IId = @IndexId

DECLARE @IxColumn      sysname
DECLARE @IxIncl         bit
DECLARE @Desc         bit
DECLARE @IxIsIncl      bit
DECLARE @IxFirstColumn bit

SET @IxIsIncl = 0
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

SET @sCreateSql = @sCreateSql + @Tab + '[' + @IxColumn + ']'
-- check if ASC or DESC
IF @IxIsIncl = 0
BEGIN
IF @Desc = 1
BEGIN
SET @sCreateSql = @sCreateSql + ' DESC'
END
ELSE
BEGIN
SET @sCreateSql = @sCreateSql + ' ASC'
END
END

FETCH NEXT
FROM IndexColumns_cursor
INTO @IxColumn, @IxIncl, @Desc
END

CLOSE IndexColumns_cursor
DEALLOCATE IndexColumns_cursor

SET @sCreateSql = @sCreateSql + @NewLine + ') '

IF @IncludeFillFactor = 1
BEGIN
SET @sCreateSql = @sCreateSql + @NewLine + 'WITH (FillFactor = ' + CAST(@FillFactor AS varchar(13)) + ')' + @NewLine
END
--END IF

IF @IncludeFileGroup = 1
BEGIN
SET @sCreateSql = @sCreateSql + 'ON ['+ @FileGroupName + ']' + @NewLine
END
ELSE
BEGIN
SET @sCreateSql = @sCreateSql + @NewLine
END
--END IF

PRINT '-- **********************************************************************'
PRINT @sIndexDesc
PRINT '-- **********************************************************************'

IF @IncludeDrop <> 1
   BEGIN
               Set @sDropSql = ''
            END

         If @IncludeDrop=0 and @IncludeCheck=1
            Begin
               Set @RunSQL = 'IF NOT EXISTS(SELECT * FROM ' + @FrmServ + @FrmDb + 'sys.indexes WHERE name = ''' +@IndexName+''' AND object_id = OBJECT_ID('''+@TableName+'''))'
                         + @NewLine +
                         'Begin'
            End
         if @IncludeTryCatch=1
            Begin
               Set @RunSQL = @RunSQL + @NewLine +
                  'Begin Try'
            end
--Main Statement
Set @RunSQL = @RunSQL + @NewLine + @sCreateSql

         if @IncludeTryCatch=1
         Begin
               Set   @RunSQL = @RunSQL   + @NewLine +
                     'End Try'      + @NewLine +
                     'Begin Catch'   + @NewLine +
                     '   RAISERROR (''The Index ' + @IndexName + ' on Table ' + @ToServ + @ToDb + @ToSch +@TableName+' could not be created'', 11,1)' + @NewLine +
                     'End Catch'
         End
         If @IncludeDrop=0 and @IncludeCheck=1
            Begin
               Set   @RunSQL = @RunSQL
                        + @NewLine +
                         'End'
            End

-- Update the strings if they are to go to another server
IF @ToServ <> @FrmServ
Begin
   Set @sDropSql = @UseServ + REPLACE(@sDropSql, '''','''''') + '''' + @NewLine
   Set @RunSQL = @UseServ + REPLACE(@RunSQL, '''','''''') + '''' + @NewLine
End

--Print the statements
PRINT @sDropSql
PRINT @RunSQL

--See if you want to Execute the scripts
IF @Exe = 1
Begin
   Exec (@sDropSql)
   Exec (@RunSql)
end

--Move to the next record
      FETCH NEXT
FROM Indexes_cursor
INTO @SchemaName, @TableName, @TableId, @IndexName, @IndexId, @FileGroupName, @FillFactor
END

--At the end Clean up
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