July 5, 2012 at 9:23 am
Could you please help me out how to script out all 'indexes create script' from user database?
Thank you in advance:-)
July 5, 2012 at 9:31 am
-- 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].
, changing that to [dbo].
-- 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
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
July 5, 2012 at 9:39 am
here's another version i had slapped together once;
this is specific to SQL 2008 and above, becuase it's also scripting filtered indexes and include columns;
i never bothered fleshing out the partitions section of the scripts, but left the code in comments for reference.
if you run this on SQL 2005, you'll have to change it, as you get errors on the has_filter column and a few other things.
;WITH MyStagingData
AS
(
(SELECT
SCH.schema_id,
SCH.[name] COLLATE database_default AS SCHEMA_NAME,
OBJS.[object_id],
OBJS.[name] COLLATE database_default AS OBJECT_NAME,
IDX.index_id,
ISNULL(IDX.[name], '---') COLLATE database_default AS index_name,
partstatz.Rows,
partstatz.SizeMB,
INDEXPROPERTY(OBJS.[object_id], IDX.[name], 'IndexDepth') AS IndexDepth,
IDX.type,
IDX.type_desc COLLATE database_default AS type_desc,
IDX.is_unique,
IDX.is_primary_key,
IDX.is_unique_constraint,
IDX.has_filter,
IDX.filter_definition,
ISNULL(Index_Columns.index_columns_key, '---') COLLATE database_default AS index_columns_key,
ISNULL(Index_Columns.index_columns_include, '---') COLLATE database_default AS index_columns_include,
IDX.is_padded,
IDX.fill_factor,
IDX.ignore_dup_key,
STATZ.no_recompute ,
IDX.allow_row_locks,
IDX.allow_page_locks,
PARTZ.data_compression_desc
FROM sys.objects OBJS
INNER JOIN sys.schemas SCH
ON OBJS.schema_id = SCH.schema_id
INNER JOIN sys.indexes IDX
ON OBJS.[object_id] = IDX.[object_id]
INNER JOIN (SELECT
[OBJECT_ID],
index_id,
SUM(row_count) AS ROWS,
CONVERT(NUMERIC(19, 3), CONVERT(NUMERIC(19, 3), SUM(in_row_reserved_page_count+lob_reserved_page_count+row_overflow_reserved_page_count))/CONVERT(NUMERIC(19, 3), 128)) AS SizeMB
FROM sys.dm_db_partition_stats STATS
GROUP BY
[OBJECT_ID],
index_id) AS partstatz
ON IDX.[object_id] = partstatz.[object_id]
AND IDX.index_id = partstatz.index_id
INNER JOIN sys.partitions PARTZ
ON IDX.[object_id] = PARTZ.[object_id]
AND IDX.index_id = PARTZ.index_id
INNER JOIN sys.stats STATZ
ON IDX.[object_id] = STATZ.[object_id]
AND IDX.index_id = STATZ.stats_id
CROSS APPLY (SELECT
LEFT(index_columns_key, LEN(index_columns_key) - 1) COLLATE database_default AS index_columns_key,
LEFT(index_columns_include, LEN(index_columns_include) - 1) COLLATE database_default AS index_columns_include
FROM (SELECT
(SELECT
colz.[name] + CASE WHEN IXCOLS.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ',' + ' ' COLLATE database_default
FROM sys.index_columns IXCOLS
INNER JOIN sys.columns colz
ON IXCOLS.column_id = colz.column_id
AND IXCOLS.[object_id] = colz.[object_id]
WHERE IXCOLS.is_included_column = 0
AND IDX.[object_id] = IXCOLS.[object_id]
AND IDX.index_id = IXCOLS.index_id
ORDER BY
key_ordinal
FOR XML PATH('')) AS index_columns_key,
(SELECT
colz.[name] + ',' + ' ' COLLATE database_default
FROM sys.index_columns IXCOLS
INNER JOIN sys.columns colz
ON IXCOLS.column_id = colz.column_id
AND IXCOLS.[object_id] = colz.[object_id]
WHERE IXCOLS.is_included_column = 1
AND IDX.[object_id] = IXCOLS.[object_id]
AND IDX.index_id = IXCOLS.index_id
ORDER BY
index_column_id
FOR XML PATH('')) AS index_columns_include) AS Index_Columns) AS Index_Columns)
)
SELECT
'CREATE '
+ CASE WHEN MyStagingData.is_unique = 1 THEN ' UNIQUE' ELSE ' ' END
+ CASE WHEN MyStagingData.type_desc <> 'HEAP' THEN ' ' + MyStagingData.type_desc ELSE ' ' END
+ ' INDEX '
+ quotename(MyStagingData.index_name)
+ ' ON '
+ + quotename(MyStagingData.schema_name)
+'.'
+ quotename(MyStagingData.object_name)
+ ' (' + MyStagingData.index_columns_key + ')'
+ CASE WHEN MyStagingData.index_columns_include <> '---' THEN ' INCLUDE (' + MyStagingData.index_columns_include + ')' ELSE '' END
+ CASE WHEN MyStagingData.has_filter = 1 THEN ' WHERE ' + MyStagingData.filter_definition ELSE '' END
+ ' WITH '
+ ' PAD_INDEX = ' + CASE WHEN is_padded = 1 THEN 'ON' ELSE 'OFF' END + ','
+ ' FILLFACTOR = ' + convert(varchar,MyStagingData.fill_factor) + ','
+ ' IGNORE_DUP_KEY = ' + CASE WHEN ignore_dup_key = 1 THEN 'ON' ELSE 'OFF' END + ','
+ ' STATISTICS_NORECOMPUTE = ' + CASE WHEN no_recompute = 1 THEN 'ON' ELSE 'OFF' END + ','
+ ' ALLOW_ROW_LOCKS = ' + CASE WHEN allow_row_locks = 1 THEN 'ON' ELSE 'OFF' END + ','
+ ' ALLOW_PAGE_LOCKS = ' + CASE WHEN allow_page_locks = 1 THEN 'ON' ELSE 'OFF' END + ','
+ ' DATA_COMPRESSION = ' + data_compression_desc
-- + ','
--+ ' [ ON PARTITIONS ( { <partition_number_expression> | <range> } '
--+ ' [ , ...n ] ) ] '
,* FROM MyStagingData
WHERE MyStagingData.SCHEMA_NAME <> 'sys'
Lowell
July 5, 2012 at 9:47 am
Hi thanks for quick reply. this script working on sysdatabases only
I want create user database indexes please provide the script:-)
Thanks in Adavance:-)
July 5, 2012 at 9:48 am
AAKR (7/5/2012)
Hi thanks for quick reply. this script working on sysdatabases onlyI want create user database indexes please provide the script:-)
Thanks in Adavance:-)
which script are you talking about?
did you run the script while in the user database itself or did you run it in master??
Lowell
July 5, 2012 at 9:54 am
AAKR (7/5/2012)
Hi thanks for quick reply. this script working on sysdatabases onlyI want create user database indexes please provide the script:-)
Thanks in Adavance:-)
Both scripts provided should do what you are asking.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 5, 2012 at 9:54 am
I ran this script in Master and one of my user database
1. When I ran this script in Master database its executing without error
2. while executing this script in user database its showing following error
Msg 207, Level 16, State 1, Line 44
Invalid column name 'object_id'.
Msg 207, Level 16, State 1, Line 11
Invalid column name 'Rows'.
January 27, 2015 at 10:15 am
I was looking for the same script, had a hard drive malfunction(with no backup) and lost my script library. so instead of reinventing the wheel.
anywho... I am getting the error Invalid column name 'filter_definition'.
No matter what script I find online that others have written, getting the same invalid column
January 27, 2015 at 10:21 am
Lynn Hendricks (1/27/2015)
I was looking for the same script, had a hard drive malfunction(with no backup) and lost my script library. so instead of reinventing the wheel.anywho... I am getting the error Invalid column name 'filter_definition'.
No matter what script I find online that others have written, getting the same invalid column
if you run this on SQL 2005, you'll have to change it, as you get errors on the has_filter column and a few other things.
'filter_definition' was added in 2008 and above, so you'd need to modify this to not use any of the filter related columns or compresison related columns when you run this on 2005.
i just tested this modified version on a 2005 instance:
;WITH MyStagingData
AS
(
(SELECT
SCH.schema_id,
SCH.[name] COLLATE database_default AS SCHEMA_NAME,
OBJS.[object_id],
OBJS.[name] COLLATE database_default AS OBJECT_NAME,
IDX.index_id,
ISNULL(IDX.[name], '---') COLLATE database_default AS index_name,
partstatz.Rows,
partstatz.SizeMB,
INDEXPROPERTY(OBJS.[object_id], IDX.[name], 'IndexDepth') AS IndexDepth,
IDX.type,
IDX.type_desc COLLATE database_default AS type_desc,
IDX.is_unique,
IDX.is_primary_key,
IDX.is_unique_constraint,
0 AS has_filter,
'' AS filter_definition,
ISNULL(Index_Columns.index_columns_key, '---') COLLATE database_default AS index_columns_key,
ISNULL(Index_Columns.index_columns_include, '---') COLLATE database_default AS index_columns_include,
IDX.is_padded,
IDX.fill_factor,
IDX.ignore_dup_key,
STATZ.no_recompute ,
IDX.allow_row_locks,
IDX.allow_page_locks,
'' AS data_compression_desc
FROM sys.objects OBJS
INNER JOIN sys.schemas SCH
ON OBJS.schema_id = SCH.schema_id
INNER JOIN sys.indexes IDX
ON OBJS.[object_id] = IDX.[object_id]
INNER JOIN (SELECT
[OBJECT_ID],
index_id,
SUM(row_count) AS ROWS,
CONVERT(NUMERIC(19, 3), CONVERT(NUMERIC(19, 3), SUM(in_row_reserved_page_count+lob_reserved_page_count+row_overflow_reserved_page_count))/CONVERT(NUMERIC(19, 3), 128)) AS SizeMB
FROM sys.dm_db_partition_stats STATS
GROUP BY
[OBJECT_ID],
index_id) AS partstatz
ON IDX.[object_id] = partstatz.[object_id]
AND IDX.index_id = partstatz.index_id
INNER JOIN sys.partitions PARTZ
ON IDX.[object_id] = PARTZ.[object_id]
AND IDX.index_id = PARTZ.index_id
INNER JOIN sys.stats STATZ
ON IDX.[object_id] = STATZ.[object_id]
AND IDX.index_id = STATZ.stats_id
CROSS APPLY (SELECT
LEFT(index_columns_key, LEN(index_columns_key) - 1) COLLATE database_default AS index_columns_key,
LEFT(index_columns_include, LEN(index_columns_include) - 1) COLLATE database_default AS index_columns_include
FROM (SELECT
(SELECT
colz.[name] + CASE WHEN IXCOLS.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ',' + ' ' COLLATE database_default
FROM sys.index_columns IXCOLS
INNER JOIN sys.columns colz
ON IXCOLS.column_id = colz.column_id
AND IXCOLS.[object_id] = colz.[object_id]
WHERE IXCOLS.is_included_column = 0
AND IDX.[object_id] = IXCOLS.[object_id]
AND IDX.index_id = IXCOLS.index_id
ORDER BY
key_ordinal
FOR XML PATH('')) AS index_columns_key,
(SELECT
colz.[name] + ',' + ' ' COLLATE database_default
FROM sys.index_columns IXCOLS
INNER JOIN sys.columns colz
ON IXCOLS.column_id = colz.column_id
AND IXCOLS.[object_id] = colz.[object_id]
WHERE IXCOLS.is_included_column = 1
AND IDX.[object_id] = IXCOLS.[object_id]
AND IDX.index_id = IXCOLS.index_id
ORDER BY
index_column_id
FOR XML PATH('')) AS index_columns_include) AS Index_Columns) AS Index_Columns)
)
SELECT
'CREATE '
+ CASE WHEN MyStagingData.is_unique = 1 THEN ' UNIQUE' ELSE ' ' END
+ CASE WHEN MyStagingData.type_desc <> 'HEAP' THEN ' ' + MyStagingData.type_desc ELSE ' ' END
+ ' INDEX '
+ quotename(MyStagingData.index_name)
+ ' ON '
+ + quotename(MyStagingData.schema_name)
+'.'
+ quotename(MyStagingData.object_name)
+ ' (' + MyStagingData.index_columns_key + ')'
+ CASE WHEN MyStagingData.index_columns_include <> '---' THEN ' INCLUDE (' + MyStagingData.index_columns_include + ')' ELSE '' END
+ CASE WHEN MyStagingData.has_filter = 1 THEN ' WHERE ' + MyStagingData.filter_definition ELSE '' END
+ ' WITH '
+ ' PAD_INDEX = ' + CASE WHEN is_padded = 1 THEN 'ON' ELSE 'OFF' END + ','
+ ' FILLFACTOR = ' + convert(varchar,MyStagingData.fill_factor) + ','
+ ' IGNORE_DUP_KEY = ' + CASE WHEN ignore_dup_key = 1 THEN 'ON' ELSE 'OFF' END + ','
+ ' STATISTICS_NORECOMPUTE = ' + CASE WHEN no_recompute = 1 THEN 'ON' ELSE 'OFF' END + ','
+ ' ALLOW_ROW_LOCKS = ' + CASE WHEN allow_row_locks = 1 THEN 'ON' ELSE 'OFF' END + ','
+ ' ALLOW_PAGE_LOCKS = ' + CASE WHEN allow_page_locks = 1 THEN 'ON' ELSE 'OFF' END
----+ ','
----+ ' DATA_COMPRESSION = ' + data_compression_desc
-- + ','
--+ ' [ ON PARTITIONS ( { <partition_number_expression> | <range> } '
--+ ' [ , ...n ] ) ] '
,* FROM MyStagingData
WHERE MyStagingData.SCHEMA_NAME <> 'sys'
Lowell
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply