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 Friday, May 18, 2012 5:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 6, 2013 3:08 PM
Points: 1, Visits: 7
nvrm my post, i was not using the latest version!

And thx for the nice script!
Post #1302911
Posted Thursday, December 6, 2012 8:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 18, 2014 7:37 AM
Points: 4, Visits: 56
Very handy script, thanks for your efforts.

Ali
Post #1393542
Posted Tuesday, January 22, 2013 8:57 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 35,218, Visits: 31,677
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1410086
Posted Tuesday, January 22, 2013 9:36 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:23 AM
Points: 338, Visits: 1,429
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
Post #1410116
Posted Friday, February 8, 2013 5:30 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:11 AM
Points: 339, Visits: 973
Does this work and how
Post #1417625
Posted Friday, February 8, 2013 8:07 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 35,218, Visits: 31,677
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1417725
Posted Friday, February 8, 2013 8:38 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:23 AM
Points: 338, Visits: 1,429
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

Post #1417744
Posted Friday, May 31, 2013 10:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 31, 2013 10:34 AM
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

@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


Post #1458795
Posted Friday, May 31, 2013 10:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 31, 2013 10:34 AM
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

Post #1458801
Posted Tuesday, June 11, 2013 12:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 7, Visits: 297
....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 :)


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

Post #1462327
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse