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 «««1234»»

Script all Indexes Expand / Collapse
Author
Message
Posted Monday, August 30, 2010 8:48 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 12:57 PM
Points: 880, Visits: 2,433
To cross-pollinate this article discussion with a closely related article that includes a very simple SQL statement for limited scripting of SQL 2005 indexes:

http://www.sqlservercentral.com/scripts/T-SQL/71058/
Post #977368
Posted Wednesday, September 1, 2010 4:21 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 22, 2011 2:57 AM
Points: 95, Visits: 140
Sorry Guys I was on a vacation so could not amend the script with the necessary changes. comments and enhancements are much appreciated. I am glad that the script is quite helpful to our sql community.


Regards
Murali
Post #978620
Posted Saturday, October 16, 2010 3:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 6, 2013 7:58 AM
Points: 4, Visits: 52
Hi,

Great script.
I've done minor changes to include :
- schema
- drop script
- commented filter_definition for SQL 2005
- Added the ability to generate only for one table (@TableToScript='<your table>' and @SchemaToScript='<your schema>', for all, set them to NULL)

-- Script out indexes completely, including both PK's and regular indexes, each clustered or nonclustered.
-- DOES NOT HANDLE COMPRESSION; that's ok, since 2008 R2 RTM benchmarking shows it's faster and results in smaller indexes to insert uncompressed and then compress later
-- HARDCODES [dbo] schema (i.e. it doesn't say [JohnDoe].[table], changing that to [dbo].[table]
-- originally from http://www.sqlservercentral.com/Forums/Topic961088-2753-2.aspx

DECLARE
@IdxSchema SYSNAME,
@idxTableName SYSNAME,
@idxTableID INT,
@idxname SYSNAME,
@idxid INT,
@colCount INT,
@IxColumn SYSNAME,
@IxFirstColumn BIT,
@ColumnIDInTable INT,
@ColumnIDInIndex INT,
@IsIncludedColumn INT,
@sIncludeCols VARCHAR(MAX),
@sIndexCols VARCHAR(MAX),
@sSQL VARCHAR(MAX),
@sSQLDrop VARCHAR(MAX),
@sParamSQL VARCHAR(MAX),
@sFilterSQL VARCHAR(MAX),
@location SYSNAME,
@IndexCount INT,
@CurrentIndex INT,
@CurrentCol INT,
@Name VARCHAR(128),
@IsPrimaryKey TINYINT,
@Fillfactor INT,
@FilterDefinition VARCHAR(MAX),
@IsClustered BIT, -- used solely for putting information into the result table
@TableToScript SYSNAME,
@SchemaToScript SYSNAME

SET @TableToScript=NULL
SET @SchemaToScript=NULL

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].[#IndexSQL]'))
DROP TABLE [dbo].[#IndexSQL]

CREATE TABLE #IndexSQL
( SchemaName VARCHAR(128) NOT NULL
,TableName VARCHAR(128) NOT NULL
,IndexName VARCHAR(128) NOT NULL
,IsClustered BIT NOT NULL
,IsPrimaryKey BIT NOT NULL
,IndexCreateSQL VARCHAR(max) NOT NULL
,IndexDropSQL VARCHAR(max) NOT NULL
)

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].[#IndexListing]'))
DROP TABLE [dbo].[#IndexListing]

CREATE TABLE #IndexListing
(
[IndexListingID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[SchemaName] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TableName] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ObjectID] INT NOT NULL,
[IndexName] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IndexID] INT NOT NULL,
[IsPrimaryKey] TINYINT NOT NULL,
[FillFactor] INT,
[FilterDefinition] NVARCHAR(MAX) NULL
)

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].[#ColumnListing]'))
DROP TABLE [dbo].[#ColumnListing]

CREATE TABLE #ColumnListing
(
[ColumnListingID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[ColumnIDInTable] INT NOT NULL,
[Name] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ColumnIDInIndex] INT NOT NULL,
[IsIncludedColumn] BIT NULL
)

IF ISNULL(@TableToScript,'')=''
BEGIN
INSERT INTO #IndexListing( [SchemaName], [TableName], [ObjectID], [IndexName], [IndexID], [IsPrimaryKey], [FILLFACTOR], [FilterDefinition] )
SELECT ss.name, OBJECT_NAME(si.object_id), si.object_id, si.name, si.index_id, si.Is_Primary_Key, si.Fill_Factor, NULL --si.filter_definition
FROM sys.indexes si
LEFT OUTER JOIN information_schema.table_constraints tc ON si.name = tc.constraint_name AND OBJECT_NAME(si.object_id) = tc.table_name
INNER JOIN sys.objects so ON so.object_id=si.object_id
INNER JOIN sys.schemas ss ON ss.schema_id=so.schema_id
WHERE OBJECTPROPERTY(si.object_id, 'IsUserTable') = 1
ORDER BY OBJECT_NAME(si.object_id), si.index_id
END
ELSE
BEGIN
INSERT INTO #IndexListing( [SchemaName], [TableName], [ObjectID], [IndexName], [IndexID], [IsPrimaryKey], [FILLFACTOR], [FilterDefinition] )
SELECT ss.name, OBJECT_NAME(si.object_id), si.object_id, si.name, si.index_id, si.Is_Primary_Key, si.Fill_Factor, NULL --si.filter_definition
FROM sys.indexes si
LEFT OUTER JOIN information_schema.table_constraints tc ON si.name = tc.constraint_name AND OBJECT_NAME(si.object_id) = tc.table_name
INNER JOIN sys.objects so ON so.object_id=si.object_id
INNER JOIN sys.schemas ss ON so.schema_id=ss.schema_id
WHERE OBJECTPROPERTY(si.object_id, 'IsUserTable') = 1 AND OBJECT_NAME(si.OBJECT_ID)=@TableToScript
and ss.name=@SchemaToScript
ORDER BY OBJECT_NAME(si.object_id), si.index_id
END
SELECT @IndexCount = @@ROWCOUNT, @CurrentIndex = 1
WHILE @CurrentIndex <= @IndexCount
BEGIN

SELECT @IdxSchema=[SchemaName],
@idxTableName = [TableName],
@idxTableID = [ObjectID],
@idxname = [IndexName],
@idxid = [IndexID],
@IsPrimaryKey = [IsPrimaryKey],
@FillFactor = [FILLFACTOR],
@FilterDefinition = [FilterDefinition]
FROM #IndexListing
WHERE [IndexListingID] = @CurrentIndex

-- So - it is either an index or a constraint
-- Check if the index is unique
IF (@IsPrimaryKey = 1)
BEGIN
SET @sSQL = 'ALTER TABLE ['+@IdxSchema+'].[' + @idxTableName + '] ADD CONSTRAINT [' + @idxname + '] PRIMARY KEY '
SET @sSQLDrop='ALTER TABLE ['+@IdxSchema+'].[' + @idxTableName + '] DROP CONSTRAINT [' + @idxname + ']'

-- Check if the index is clustered
IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsClustered') = 0)
BEGIN
SET @sSQL = @sSQL + 'NON'
SET @IsClustered = 0
END
ELSE
BEGIN
SET @IsClustered = 1
END
SET @sSQL = @sSQL + 'CLUSTERED' + CHAR(13) + '(' + CHAR(13)
END
ELSE
BEGIN
SET @sSQL = 'CREATE '
SET @sSQLDrop = 'DROP INDEX [' + @idxname + '] ON ['+@IdxSchema+'].[' + @idxTableName + ']'
-- Check if the index is unique
IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsUnique') = 1)
BEGIN
SET @sSQL = @sSQL + 'UNIQUE '
END
-- Check if the index is clustered
IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsClustered') = 1)
BEGIN
SET @sSQL = @sSQL + 'CLUSTERED '
SET @IsClustered = 1
END
ELSE
BEGIN
SET @IsClustered = 0
END

SELECT
@sSQL = @sSQL + 'INDEX [' + @idxname + '] ON ['+@IdxSchema+'].[' + @idxTableName + ']' + CHAR(13) + '(' + CHAR(13),
@colCount = 0
END

-- Get the number of cols in the index
SELECT @colCount = COUNT(*)
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 = @idxtableid AND index_id = @idxid AND ic.is_included_column = 0

-- Get the file group info
SELECT @location = f.[name]
FROM sys.indexes i
INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id]
WHERE o.object_id = @idxTableID AND i.index_id = @idxid

-- Get all columns of the index
INSERT INTO #ColumnListing( [ColumnIDInTable], [Name], [ColumnIDInIndex],[IsIncludedColumn] )
SELECT sc.column_id, sc.name, ic.index_column_id, ic.is_included_column
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 = @idxTableID AND index_id = @idxid
ORDER BY ic.index_column_id

IF @@ROWCOUNT > 0
BEGIN

SELECT @CurrentCol = 1

SELECT @IxFirstColumn = 1, @sIncludeCols = '', @sIndexCols = ''

WHILE @CurrentCol <= @ColCount
BEGIN
SELECT @ColumnIDInTable = ColumnIDInTable,
@Name = Name,
@ColumnIDInIndex = ColumnIDInIndex,
@IsIncludedColumn = IsIncludedColumn
FROM #ColumnListing
WHERE [ColumnListingID] = @CurrentCol

IF @IsIncludedColumn = 0
BEGIN

SET @sIndexCols = CHAR(9) + @sIndexCols + '[' + @Name + '] '

-- Check the sort order of the index cols ????????
IF (INDEXKEY_PROPERTY (@idxTableID,@idxid,@ColumnIDInIndex,'IsDescending')) = 0
BEGIN
SET @sIndexCols = @sIndexCols + ' ASC '
END
ELSE
BEGIN
SET @sIndexCols = @sIndexCols + ' DESC '
END

IF @CurrentCol < @colCount
BEGIN
SET @sIndexCols = @sIndexCols + ', '
END

END
ELSE
BEGIN
-- Check for any include columns
IF LEN(@sIncludeCols) > 0
BEGIN
SET @sIncludeCols = @sIncludeCols + ','
END

SET @sIncludeCols = @sIncludeCols + '[' + @IxColumn + ']'

END

SET @CurrentCol = @CurrentCol + 1
END

TRUNCATE TABLE #ColumnListing

--append to the result
IF LEN(@sIncludeCols) > 0
SET @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ') ' + ' INCLUDE ( ' + @sIncludeCols + ' ) '
ELSE
SET @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ') '

-- Add filtering
IF @FilterDefinition IS NOT NULL
SET @sFilterSQL = ' WHERE ' + @FilterDefinition + ' ' + CHAR(13)
ELSE
SET @sFilterSQL = ''

-- Build the options
SET @sParamSQL = 'WITH ( PAD_INDEX = '

IF INDEXPROPERTY(@idxTableID, @idxname, 'IsPadIndex') = 1
SET @sParamSQL = @sParamSQL + 'ON,'
ELSE
SET @sParamSQL = @sParamSQL + 'OFF,'

SET @sParamSQL = @sParamSQL + ' ALLOW_PAGE_LOCKS = '


IF INDEXPROPERTY(@idxTableID, @idxname, 'IsPageLockDisallowed') = 0
SET @sParamSQL = @sParamSQL + 'ON,'
ELSE
SET @sParamSQL = @sParamSQL + 'OFF,'

SET @sParamSQL = @sParamSQL + ' ALLOW_ROW_LOCKS = '

IF INDEXPROPERTY(@idxTableID, @idxname, 'IsRowLockDisallowed') = 0
SET @sParamSQL = @sParamSQL + 'ON,'
ELSE
SET @sParamSQL = @sParamSQL + 'OFF,'


SET @sParamSQL = @sParamSQL + ' STATISTICS_NORECOMPUTE = '

-- THIS DOES NOT WORK PROPERLY; IsStatistics only says what generated the last set, not what it was set to do.
IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsStatistics') = 1)
SET @sParamSQL = @sParamSQL + 'ON'
ELSE
SET @sParamSQL = @sParamSQL + 'OFF'

-- Fillfactor 0 is actually not a valid percentage on SQL 2008 R2
IF ISNULL( @FillFactor, 90 ) <> 0
SET @sParamSQL = @sParamSQL + ' ,FILLFACTOR = ' + CAST( ISNULL( @FillFactor, 90 ) AS VARCHAR(3) )


IF (@IsPrimaryKey = 1) -- DROP_EXISTING isn't valid for PK's
BEGIN
SET @sParamSQL = @sParamSQL + ' ) '
END
ELSE
BEGIN
SET @sParamSQL = @sParamSQL + ' ,DROP_EXISTING = ON ) '
END

SET @sSQL = @sIndexCols + CHAR(13) + @sFilterSQL + CHAR(13) + @sParamSQL

-- 2008 R2 allows ON [filegroup] for primary keys as well, negating the old "IF THE INDEX IS NOT A PRIMARY KEY - ADD THIS - ELSE DO NOT" IsPrimaryKey IF statement
SET @sSQL = @sSQL + ' ON [' + @location + ']'

--PRINT @sIndexCols + CHAR(13)
INSERT INTO #IndexSQL (SchemaName, TableName, IndexName, IsClustered, IsPrimaryKey, IndexCreateSQL, IndexDropSQL)
VALUES (@IdxSchema, @idxTableName, @idxName, @IsClustered, @IsPrimaryKey, @sSQL, @sSQLDrop)

END

SET @CurrentIndex = @CurrentIndex + 1
END

SELECT * FROM #IndexSQL ORDER BY 1,2 --WHERE IsPrimaryKey=0


Jean-Marc
Post #1005681
Posted Monday, October 18, 2010 7:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 26, 2011 1:32 PM
Points: 146, Visits: 327
Nice Add Jean!
Post #1006203
Posted Friday, February 4, 2011 6:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 23, 2013 1:46 PM
Points: 3, Visits: 29
Hi, I'm so excited about this code! However, I'm not getting included columns in the create index statement. I'm running SQL 2008 R2.

For example, here's what I'm getting from the script:

CREATE INDEX [IX_xyz] ON [dbo].[Table] ( [Key1] ASC ) WITH ( PAD_INDEX = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, STATISTICS_NORECOMPUTE = OFF ,DROP_EXISTING = ON ) ON [PRIMARY]

Here's what I get from scripting from SSMS:

CREATE NONCLUSTERED INDEX [IX_xyz] N [dbo].[Table]
(
[Key1] ASC
)
INCLUDE ( [DebitOrCredit],
[GroupCurrencyAmount],
[LocalAmount],
[TransactionAmount]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Post #1058686
Posted Friday, February 4, 2011 6:45 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 22, 2011 2:57 AM
Points: 95, Visits: 140
Lisa

Can you please send me the source code you are using to generate the script , let me check and comeback to you.

Regards
Murali
Post #1058694
Posted Friday, February 4, 2011 7:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 23, 2013 1:46 PM
Points: 3, Visits: 29
Wow, thanks for the quick reply! I copied the script posted above by Jean-Marc Burgstahler on 10/16/2010 and made no changes to it. I also tried copying the script found here - http://www.sqlservercentral.com/scripts/Indexing/70737/ - and got the same results, no included columns.
Post #1058712
Posted Friday, February 4, 2011 9:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 23, 2013 1:46 PM
Points: 3, Visits: 29
Hi, I changed the script (original script, not Jean-Marc's version) so that included columns are working. Here's what changed:

1) The section that builds included columns was using variable @IxColumn which was never set.

2) This select statement shown below in the original code was omitting included columns. It was changed to include them and now also includes a separate count called @colCountMinusIncludedColumns which is used to determine whether to add a comma to the list of index keys.

Old code:
SELECT @colCount = COUNT(*),
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 = @idxtableid AND index_id = @idxid
AND ic.is_included_column = 0

New code:
SELECT @colCount = COUNT(*),
@colCountMinusIncludedColumns = SUM(CASE ic.is_included_column WHEN 0 THEN 1 ELSE 0 END)
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 = @idxtableid AND index_id = @idxid

Hope this helps!

-- Script out indexes completely, including both PK's and regular indexes, each clustered or nonclustered.
-- DOES NOT HANDLE COMPRESSION; that's ok, since 2008 R2 RTM benchmarking shows it's faster and results in smaller indexes to insert uncompressed and then compress later
-- HARDCODES [dbo] schema (i.e. it doesn't say [JohnDoe].[table], changing that to [dbo].[table]
-- originally from http://www.sqlservercentral.com/Forums/Topic961088-2753-2.aspx
SET NOCOUNT ON
DECLARE
@idxTableName SYSNAME,
@idxTableID INT,
@idxname SYSNAME,
@idxid INT,
@colCount INT,
@colCountMinusIncludedColumns INT,
@IxColumn SYSNAME,
@IxFirstColumn BIT,
@ColumnIDInTable INT,
@ColumnIDInIndex INT,
@IsIncludedColumn INT,
@sIncludeCols VARCHAR(MAX),
@sIndexCols VARCHAR(MAX),
@sSQL VARCHAR(MAX),
@sParamSQL VARCHAR(MAX),
@sFilterSQL VARCHAR(MAX),
@location SYSNAME,
@IndexCount INT,
@CurrentIndex INT,
@CurrentCol INT,
@Name VARCHAR(128),
@IsPrimaryKey TINYINT,
@Fillfactor INT,
@FilterDefinition VARCHAR(MAX),
@IsClustered BIT -- used solely for putting information into the result table

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].[#IndexSQL]'))
DROP TABLE [dbo].[#IndexSQL]

CREATE TABLE #IndexSQL
( TableName VARCHAR(128) NOT NULL
,IndexName VARCHAR(128) NOT NULL
,IsClustered BIT NOT NULL
,IsPrimaryKey BIT NOT NULL
,IndexCreateSQL VARCHAR(max) NOT NULL
)

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].[#IndexListing]'))
DROP TABLE [dbo].[#IndexListing]

CREATE TABLE #IndexListing
(
[IndexListingID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[TableName] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ObjectID] INT NOT NULL,
[IndexName] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IndexID] INT NOT NULL,
[IsPrimaryKey] TINYINT NOT NULL,
[FillFactor] INT,
[FilterDefinition] NVARCHAR(MAX) NULL
)

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].[#ColumnListing]'))
DROP TABLE [dbo].[#ColumnListing]

CREATE TABLE #ColumnListing
(
[ColumnListingID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[ColumnIDInTable] INT NOT NULL,
[Name] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ColumnIDInIndex] INT NOT NULL,
[IsIncludedColumn] BIT NULL
)

INSERT INTO #IndexListing( [TableName], [ObjectID], [IndexName], [IndexID], [IsPrimaryKey], [FILLFACTOR], [FilterDefinition] )
SELECT OBJECT_NAME(si.object_id), si.object_id, si.name, si.index_id, si.Is_Primary_Key, si.Fill_Factor, si.filter_definition
FROM sys.indexes si
LEFT OUTER JOIN information_schema.table_constraints tc ON si.name = tc.constraint_name AND OBJECT_NAME(si.object_id) = tc.table_name
WHERE OBJECTPROPERTY(si.object_id, 'IsUserTable') = 1
ORDER BY OBJECT_NAME(si.object_id), si.index_id

SELECT @IndexCount = @@ROWCOUNT, @CurrentIndex = 1

WHILE @CurrentIndex <= @IndexCount
BEGIN

SELECT @idxTableName = [TableName],
@idxTableID = [ObjectID],
@idxname = [IndexName],
@idxid = [IndexID],
@IsPrimaryKey = [IsPrimaryKey],
@FillFactor = [FILLFACTOR],
@FilterDefinition = [FilterDefinition]
FROM #IndexListing
WHERE [IndexListingID] = @CurrentIndex

-- So - it is either an index or a constraint
-- Check if the index is unique
IF (@IsPrimaryKey = 1)
BEGIN
SET @sSQL = 'ALTER TABLE [dbo].[' + @idxTableName + '] ADD CONSTRAINT [' + @idxname + '] PRIMARY KEY '
-- Check if the index is clustered
IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsClustered') = 0)
BEGIN
SET @sSQL = @sSQL + 'NON'
SET @IsClustered = 0
END
ELSE
BEGIN
SET @IsClustered = 1
END
SET @sSQL = @sSQL + 'CLUSTERED' + CHAR(13) + '(' + CHAR(13)
END
ELSE
BEGIN
SET @sSQL = 'CREATE '
-- Check if the index is unique
IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsUnique') = 1)
BEGIN
SET @sSQL = @sSQL + 'UNIQUE '
END
-- Check if the index is clustered
IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsClustered') = 1)
BEGIN
SET @sSQL = @sSQL + 'CLUSTERED '
SET @IsClustered = 1
END
ELSE
BEGIN
SET @IsClustered = 0
END

SELECT
@sSQL = @sSQL + 'INDEX [' + @idxname + '] ON [dbo].[' + @idxTableName + ']' + CHAR(13) + '(' + CHAR(13),
@colCount = 0,
@colCountMinusIncludedColumns = 0
END

-- Get the nuthe mber of cols in the index
SELECT @colCount = COUNT(*),
@colCountMinusIncludedColumns = SUM(CASE ic.is_included_column WHEN 0 THEN 1 ELSE 0 END)
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 = @idxtableid AND index_id = @idxid

-- Get the file group info
SELECT @location = f.[name]
FROM sys.indexes i
INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id]
WHERE o.object_id = @idxTableID AND i.index_id = @idxid

-- Get all columns of the index
INSERT INTO #ColumnListing( [ColumnIDInTable], [Name], [ColumnIDInIndex],[IsIncludedColumn] )
SELECT sc.column_id, sc.name, ic.index_column_id, ic.is_included_column
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 = @idxTableID AND index_id = @idxid
ORDER BY ic.index_column_id

IF @@ROWCOUNT > 0
BEGIN

SELECT @CurrentCol = 1
SELECT @IxFirstColumn = 1, @sIncludeCols = '', @sIndexCols = ''

WHILE @CurrentCol <= @ColCount
BEGIN
SELECT @ColumnIDInTable = ColumnIDInTable,
@Name = Name,
@ColumnIDInIndex = ColumnIDInIndex,
@IsIncludedColumn = IsIncludedColumn
FROM #ColumnListing
WHERE [ColumnListingID] = @CurrentCol

IF @IsIncludedColumn = 0
BEGIN

SELECT @sIndexCols = CHAR(9) + @sIndexCols + '[' + @Name + '] '

-- Check the sort order of the index cols ????????
IF (INDEXKEY_PROPERTY (@idxTableID,@idxid,@ColumnIDInIndex,'IsDescending')) = 0
BEGIN
SET @sIndexCols = @sIndexCols + ' ASC '
END
ELSE
BEGIN
SET @sIndexCols = @sIndexCols + ' DESC '
END

IF @CurrentCol < @colCountMinusIncludedColumns
BEGIN
SET @sIndexCols = @sIndexCols + ', '
END

END
ELSE
BEGIN
-- Check for any include columns
IF LEN(@sIncludeCols) > 0
BEGIN
SET @sIncludeCols = @sIncludeCols + ','
END

SELECT @sIncludeCols = @sIncludeCols + '[' + @Name + ']'

END

SET @CurrentCol = @CurrentCol + 1
END

TRUNCATE TABLE #ColumnListing
--append to the result
IF LEN(@sIncludeCols) > 0
SET @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ') ' + ' INCLUDE ( ' + @sIncludeCols + ' ) '
ELSE
SET @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ') '

-- Add filtering
IF @FilterDefinition IS NOT NULL
SET @sFilterSQL = ' WHERE ' + @FilterDefinition + ' ' + CHAR(13)
ELSE
SET @sFilterSQL = ''

-- Build the options
SET @sParamSQL = 'WITH ( PAD_INDEX = '

IF INDEXPROPERTY(@idxTableID, @idxname, 'IsPadIndex') = 1
SET @sParamSQL = @sParamSQL + 'ON,'
ELSE
SET @sParamSQL = @sParamSQL + 'OFF,'

SET @sParamSQL = @sParamSQL + ' ALLOW_PAGE_LOCKS = '


IF INDEXPROPERTY(@idxTableID, @idxname, 'IsPageLockDisallowed') = 0
SET @sParamSQL = @sParamSQL + 'ON,'
ELSE
SET @sParamSQL = @sParamSQL + 'OFF,'

SET @sParamSQL = @sParamSQL + ' ALLOW_ROW_LOCKS = '

IF INDEXPROPERTY(@idxTableID, @idxname, 'IsRowLockDisallowed') = 0
SET @sParamSQL = @sParamSQL + 'ON,'
ELSE
SET @sParamSQL = @sParamSQL + 'OFF,'


SET @sParamSQL = @sParamSQL + ' STATISTICS_NORECOMPUTE = '

-- THIS DOES NOT WORK PROPERLY; IsStatistics only says what generated the last set, not what it was set to do.
IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsStatistics') = 1)
SET @sParamSQL = @sParamSQL + 'ON'
ELSE
SET @sParamSQL = @sParamSQL + 'OFF'

-- Fillfactor 0 is actually not a valid percentage on SQL 2008 R2
IF ISNULL( @FillFactor, 90 ) <> 0
SET @sParamSQL = @sParamSQL + ' ,FILLFACTOR = ' + CAST( ISNULL( @FillFactor, 90 ) AS VARCHAR(3) )


IF (@IsPrimaryKey = 1) -- DROP_EXISTING isn't valid for PK's
BEGIN
SET @sParamSQL = @sParamSQL + ' ) '
END
ELSE
BEGIN
SET @sParamSQL = @sParamSQL + ' ,DROP_EXISTING = ON ) '
END

SET @sSQL = @sIndexCols + CHAR(13) + @sFilterSQL + CHAR(13) + @sParamSQL

-- 2008 R2 allows ON [filegroup] for primary keys as well, negating the old "IF THE INDEX IS NOT A PRIMARY KEY - ADD THIS - ELSE DO NOT" IsPrimaryKey IF statement
SET @sSQL = @sSQL + ' ON [' + @location + ']'

--PRINT @sIndexCols + CHAR(13)
INSERT INTO #IndexSQL (TableName, IndexName, IsClustered, IsPrimaryKey, IndexCreateSQL) VALUES (@idxTableName, @idxName, @IsClustered, @IsPrimaryKey, @sSQL)

END

SET @CurrentIndex = @CurrentIndex + 1
END

SELECT * FROM #IndexSQL


Post #1058836
Posted Wednesday, January 4, 2012 8:08 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 10, 2014 7:02 AM
Points: 51, Visits: 390
I am having issues running this script against a SQL 2005 box (9.0.5000). I keep getting these errors

Msg 2715, Level 16, State 3, Line 280
Column, parameter, or variable #1: Cannot find data type SYSNAME.
Parameter or variable '@idxTableName' has an invalid data type.
Msg 2715, Level 16, State 3, Line 280
Column, parameter, or variable #3: Cannot find data type SYSNAME.
Parameter or variable '@idxName' has an invalid data type.
Msg 2715, Level 16, State 3, Line 280
Column, parameter, or variable #7: Cannot find data type SYSNAME.
Parameter or variable '@IxColumn' has an invalid data type.
Msg 2715, Level 16, State 3, Line 280
Column, parameter, or variable #17: Cannot find data type SYSNAME.
Parameter or variable '@location' has an invalid data type.


SQL_Padre
aka Robert M Bishop

"Do or do not, there is no try" -- Yoda
Post #1229969
Posted Friday, February 24, 2012 3:35 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, October 3, 2014 7:54 AM
Points: 837, Visits: 180
Here is the script after resolving minor issues with include and fill factor and added checking for index existence.

Very nice script though thank you.


DECLARE @idxTableName SYSNAME
DECLARE @idxTableID INT
DECLARE @idxname SYSNAME
DECLARE @idxid INT
DECLARE @colCount INT
DECLARE @IxColumn SYSNAME
DECLARE @IxFirstColumn BIT
DECLARE @ColumnIDInTable INT
DECLARE @ColumnIDInIndex INT
DECLARE @IsIncludedColumn INT
DECLARE @sIncludeCols VARCHAR(4000)
DECLARE @sIndexCols VARCHAR(4000)
DECLARE @sSQL VARCHAR(4000)
DECLARE @rowcnt INT
DECLARE @sParamSQL VARCHAR(4000)
DECLARE @location SYSNAME
DECLARE @fillfactor INT

-- Get all the index info
DECLARE curidx 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 objectproperty(si.object_id, 'IsUserTable') = 1
ORDER BY object_name(si.object_id)
,si.index_id

OPEN curidx

FETCH NEXT
FROM curidx
INTO @idxTableName
,@idxTableID
,@idxname
,@idxid

--loop
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @sSQL = 'IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WHERE name = ''' + @idxname + ''')' + CHAR(13)
SET @sSQL = @sSQL + 'BEGIN' + CHAR(13)
SET @sSQL = @sSQL + 'CREATE '

-- Check if the index is unique
IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsUnique') = 1)
SET @sSQL = @sSQL + 'UNIQUE '

-- Check if the index is clustered
IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsClustered') = 1)
SET @sSQL = @sSQL + 'CLUSTERED '
SET @sSQL = @sSQL + 'INDEX ' + @idxname + ' ON ' + @idxTableName + CHAR(13) + '('
SET @sSQL = @sSQL + CHAR(13)
SET @colCount = 0

SELECT @fillfactor = fill_factor
FROM sys.indexes
WHERE name = @idxname

IF ISNULL(@fillfactor, 0) = 0
SET @fillfactor = 90

-- Get the number of cols in the index
SELECT @colCount = COUNT(*)
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 = @idxtableid AND index_id = @idxid AND ic.is_included_column = 0

-- Get the file group info
SELECT @location = f.[name]
FROM sys.indexes i
INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id]
WHERE o.object_id = @idxTableID AND i.index_id = @idxid

-- Get all columns of the index
DECLARE curidxcolumn CURSOR
FOR
SELECT sc.column_id AS columnidintable
,sc.NAME
,ic.index_column_id columnidinindex
,ic.is_included_column AS isincludedcolumn
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 = @idxTableID AND index_id = @idxid
ORDER BY ic.index_column_id

SET @IxFirstColumn = 1
SET @sIncludeCols = ''
SET @sIndexCols = ''
SET @rowcnt = 0

OPEN curidxColumn

FETCH NEXT
FROM curidxColumn
INTO @ColumnIDInTable
,@IxColumn
,@ColumnIDInIndex
,@IsIncludedColumn

--loop
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @IsIncludedColumn = 0
BEGIN
SET @rowcnt = @rowcnt + 1
SET @sIndexCols = CHAR(9) + @sIndexCols + '[' + @IxColumn + ']'

-- Check the sort order of the index cols
IF (INDEXKEY_PROPERTY(@idxTableID, @idxid, @ColumnIDInIndex, 'IsDescending')) = 0
SET @sIndexCols = @sIndexCols + ' ASC '
ELSE
SET @sIndexCols = @sIndexCols + ' DESC '

IF @rowcnt < @colCount
SET @sIndexCols = @sIndexCols + ', '
END
ELSE
BEGIN
-- Check for any include columns
IF len(@sIncludeCols) > 0
SET @sIncludeCols = @sIncludeCols + ','
SET @sIncludeCols = @sIncludeCols + '[' + @IxColumn + ']'

END

FETCH NEXT
FROM curidxColumn
INTO @ColumnIDInTable
,@IxColumn
,@ColumnIDInIndex
,@IsIncludedColumn
END

CLOSE curidxColumn

DEALLOCATE curidxColumn

--append to the result
IF LEN(@sIncludeCols) > 0
SET @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ') ' + ' INCLUDE ( ' + @sIncludeCols + ' ) '
ELSE
SET @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ') '

-- Build the options
SET @sParamSQL = ' WITH (FILLFACTOR = ' + cast(isnull(@fillfactor, 90) AS VARCHAR(3)) + ', '

--set @sParamSQL = ' WITH ('
IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsPadIndex') = 1)
SET @sParamSQL = @sParamSQL + ' PAD_INDEX = ON, '
ELSE
SET @sParamSQL = @sParamSQL + ' PAD_INDEX = OFF, '

IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsPageLockDisallowed') = 1)
SET @sParamSQL = @sParamSQL + ' ALLOW_PAGE_LOCKS = ON, '
ELSE
SET @sParamSQL = @sParamSQL + ' ALLOW_PAGE_LOCKS = OFF, '

IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsRowLockDisallowed') = 1)
SET @sParamSQL = @sParamSQL + ' ALLOW_ROW_LOCKS = ON, '
ELSE
SET @sParamSQL = @sParamSQL + ' ALLOW_ROW_LOCKS = OFF, '

IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsStatistics') = 1)
SET @sParamSQL = @sParamSQL + ' STATISTICS_NORECOMPUTE = ON, '
ELSE
SET @sParamSQL = @sParamSQL + ' STATISTICS_NORECOMPUTE = OFF, '

SET @sParamSQL = @sParamSQL + ' DROP_EXISTING = ON ) '
SET @sIndexCols = @sIndexCols + CHAR(13) + @sParamSQL + ' ON [' + @location + ']' + CHAR(13) + 'END ' + CHAR(10) + 'GO' + CHAR(13)

PRINT @sIndexCols

FETCH NEXT
FROM curidx
INTO @idxTableName
,@idxTableID
,@idxname
,@idxid
END

CLOSE curidx
DEALLOCATE curidx





Post #1257283
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse