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 Wednesday, August 4, 2010 9:36 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
Murali,

Just wondering why the INCLUDE data points all have DESC after them?

Doug
Post #963560
Posted Wednesday, August 4, 2010 9:38 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
Mulali,

Also noticed the SQL for the INCLUDE doesn't compile - the [ needs to be a (.

Doug
Post #963563
Posted Wednesday, August 4, 2010 9:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 22, 2011 2:33 AM
Points: 3, Visits: 44
Hi,

The second SQL script worked for me, brilliant job!

Thanks.
Post #963572
Posted Friday, August 6, 2010 6:29 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
New version is available pls check and let me know
Post #965064
Posted Friday, August 6, 2010 6:44 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
I tweaked your code a bit:

CREATE PROCEDURE [dbo].[IndexLister]
AS
BEGIN
SET NOCOUNT ON


DECLARE
@idxTableName SYSNAME,
@idxTableID INT,
@idxname SYSNAME,
@idxid INT,
@colCount INT,
@IxColumn SYSNAME,
@IxFirstColumn BIT,
@ColumnIDInTable INT,
@ColumnIDInIndex INT,
@IsIncludedColumn INT,
@sIncludeCols VARCHAR(4000),
@sIndexCols VARCHAR(4000),
@sSQL VARCHAR(4000),
@sParamSQL VARCHAR(4000),
@location SYSNAME,
@IndexCount INT,
@CurrentIndex INT,
@CurrentCol INT,
@Name VARCHAR(128),
@IsPrimaryKey TINYINT,
@Fillfactor INT

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
)

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] )
SELECT OBJECT_NAME(si.object_id), si.object_id, si.name, si.index_id, si.Is_Primary_Key, si.Fill_Factor
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]
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'
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 '
END

SELECT
@sSQL = @sSQL + 'INDEX [' + @idxname + '] ON [dbo].[' + @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) + ') '

-- 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') = 1)
SET @sParamSQL = @sParamSQL + 'ON,'
ELSE
SET @sParamSQL = @sParamSQL + 'OFF,'

SET @sParamSQL = @sParamSQL + ' ALLOW_ROW_LOCKS = '

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

SET @sParamSQL = @sParamSQL + ' STATISTICS_NORECOMPUTE = '

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

-- Tweak this - identity = 100% - convert 0 to 100%
SET @sParamSQL = @sParamSQL + ' FILLFACTOR = ' + CAST( ISNULL( @FillFactor, 90 ) AS VARCHAR(3) ) + ', '


SET @sParamSQL = @sParamSQL + ' DROP_EXISTING = ON ) '

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

-- IF THE INDEX IS NOT A PRIMARY KEY - ADD THIS - ELSE DO NOT
IF (@IsPrimaryKey = 0)
BEGIN
SET @sIndexCols = @sIndexCols + ' ON [' + @location + ']'
END

PRINT @sIndexCols + CHAR(13)

END

SET @CurrentIndex = @CurrentIndex + 1
END

END
Post #965089
Posted Tuesday, August 24, 2010 1:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 12, 2014 12:27 PM
Points: 9, Visits: 144
I had a problem with the script generating the drop statements for my clustered and nonclustered indexs that are not Primary Keys.

The following statement would not return the object id and therefore would use the previous indexes information when it did the print statement for @sdropsql

so.object_id = object_id ( @idxTableName) and si.index_id = @idxid and si.type IN ( 1,2) -- is_primary_key = 0

I changed the @idxTableName to the actual object id, @idxTableID and it worked. Upon further investigation If I added the schema name in front of the @idxTableName it would also work correctly.
Post #974388
Posted Friday, August 27, 2010 11:07 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: Today @ 9:04 AM
Points: 889, Visits: 2,456
This is wonderful!

But does anyone know how to figure out what the DATA_COMPRESSION setting is for a given index (none, row, or page)?

It doesn't appear to be in sys.indexes or the most common property functions; the closest I've seen is compressed_page_count in sys.dm_db_index_physical_stats with 'SAMPLED' or 'DETAILED' level (clearly a bad idea to use on a large database).

Post #976596
Posted Friday, August 27, 2010 11:24 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: Today @ 9:04 AM
Points: 889, Visits: 2,456
Minor alterations at the end:

-- Commas removed from the end of ON and OFF so variable following arguments work properly with leading commas
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 @sIndexCols = @sIndexCols + CHAR(13) + @sParamSQL

-- IF THE INDEX IS NOT A PRIMARY KEY - ADD THIS - ELSE DO NOT
IF (@IsPrimaryKey = 0)
BEGIN
SET @sIndexCols = @sIndexCols + ' ON [' + @location + ']'
END
Post #976602
Posted Friday, August 27, 2010 11:48 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
Very nice followup - good add!

Doug
Post #976625
Posted Friday, August 27, 2010 12:45 PM
SSC Eights!

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

Group: General Forum Members
Last Login: Today @ 9:04 AM
Points: 889, Visits: 2,456
EDITED - minor IsClustered bug fixed

Alteration in the middle - the "Disallowed" requires a double negation logic:
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,'

NOTE: Statistics_norecompute does not show OFF work if you just re-created an index with the setting to OFF.

I've altered this to generate a #temp table for use in further scripting, rather than printed output, as well as to work with filtered indexes, and to use varchar(max) instead of varchar(4000). Primary keys now get the ON [filegroup] as well (which works on 2008 R2). All #temp tables are conditionally removed each run.

Exercises for the reader:
Conversion to purely set-based methodology.
Another column with DROP statements.
Another column with the snippet to create primary keys as part of a CREATE TABLE statement.

-- 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
@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),
@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
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 (TableName, IndexName, IsClustered, IsPrimaryKey, IndexCreateSQL) VALUES (@idxTableName, @idxName, @IsClustered, @IsPrimaryKey, @sSQL)

END

SET @CurrentIndex = @CurrentIndex + 1
END

--SELECT * FROM #IndexSQL


Post #976679
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse