Skip to content

Instantly share code, notes, and snippets.

@mklaber

mklaber/README.md

Created June 2, 2015 17:25
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save mklaber/ceeb981cafb23c4b22d0 to your computer and use it in GitHub Desktop.
Save mklaber/ceeb981cafb23c4b22d0 to your computer and use it in GitHub Desktop.
Download ZIP
ScriptCreateDropIndexes_SP with spatial indexes
Raw
README.md
Raw
ScriptCreateDropIndexes_SP.sql
IF OBJECT_ID('ScriptCreateDropIndexes_SP', 'P') IS NOT NULL
DROP PROC ScriptCreateDropIndexes_SP
GO
CREATE PROCEDURE ScriptCreateDropIndexes_SP
@TableName sysname ,
@SchemaName sysname = 'dbo' ,
@SORT_IN_TEMPDB VARCHAR(3) = 'OFF' ,
@DROP_EXISTING VARCHAR(3) = 'OFF' ,
@STATISTICS_NORECOMPUTE VARCHAR(3) = 'OFF' ,
@ONLINE VARCHAR(3) = 'OFF'
AS /*
FROM: https://gallery.technet.microsoft.com/scriptcenter/Generate-Index-using-5d8a8895
Modified to include sys.indices.type = 4 (spatial)
Parameters
@Schemaname - SchemaName to which the table belongs to. Default value 'dbo'.
@Tablename - TableName for which the Indexes need to be scripted.
@SORT_IN_TEMPDB - Runtime value for SORT_IN_TEMPDB option in create index.
Valid Values ON \ OFF. Default = 'OFF'
@DROP_EXISTING - Runtime value for DROP_EXISTING option in create index.
Valid Values ON \ OFF. Default = 'OFF'
@STATISTICS_NORECOMPUTE - Runtime value for STATISTICS_NORECOMPUTE option in create index.
Valid Values ON \ OFF. Default = 'OFF'
@ONLINE - Runtime value for ONLINE option in create index.
Valid Values ON \ OFF. Default = 'OFF'
*/
SET NOCOUNT ON
IF @SORT_IN_TEMPDB NOT IN ( 'ON', 'OFF' )
BEGIN
RAISERROR('Valid value for @SORT_IN_TEMPDB is ON \ OFF',16,1)
RETURN
END
IF @DROP_EXISTING NOT IN ( 'ON', 'OFF' )
BEGIN
RAISERROR('Valid value for @DROP_EXISTING is ON \ OFF',16,1)
RETURN
END
IF @STATISTICS_NORECOMPUTE NOT IN ( 'ON', 'OFF' )
BEGIN
RAISERROR('Valid value for @STATISTICS_NORECOMPUTE is ON \ OFF',16,1)
RETURN
END
IF @ONLINE NOT IN ( 'ON', 'OFF' )
BEGIN
RAISERROR('Valid value for @ONLINE is ON \ OFF',16,1)
RETURN
END
DECLARE @IDXTable TABLE
(
Schema_ID INT ,
Object_ID INT ,
Index_ID INT ,
SchemaName sysname ,
TableName sysname ,
IndexName sysname ,
IsPrimaryKey BIT ,
IndexType INT ,
CreateScript VARCHAR(MAX) NULL ,
DropScript VARCHAR(MAX) NULL ,
ExistsScript VARCHAR(MAX) NULL ,
Processed BIT NULL
)
INSERT INTO @IDXTable
( Schema_ID ,
Object_ID ,
Index_ID ,
SchemaName ,
TableName ,
IndexName ,
IsPrimaryKey ,
IndexType
)
SELECT ST.schema_id ,
ST.object_id ,
SI.index_id ,
SCH.name ,
ST.name ,
SI.name ,
SI.is_primary_key ,
SI.type
FROM sys.indexes SI
JOIN sys.tables ST ON SI.object_id = ST.object_id
JOIN sys.schemas SCH ON SCH.schema_id = ST.schema_id
WHERE SCH.name = @SchemaName
AND ST.name = @TableName
AND SI.type IN ( 1, 2, 3, 4 )
DECLARE @SchemaID INT
DECLARE @TableID INT
DECLARE @IndexID INT
DECLARE @isPrimaryKey BIT
DECLARE @IndexType INT
DECLARE @CreateSQL VARCHAR(MAX)
DECLARE @IndexColsSQL VARCHAR(MAX)
DECLARE @WithSQL VARCHAR(MAX)
DECLARE @IncludeSQL VARCHAR(MAX)
DECLARE @WhereSQL VARCHAR(MAX)
DECLARE @SQL VARCHAR(MAX)
DECLARE @DropSQL VARCHAR(MAX)
DECLARE @ExistsSQL VARCHAR(MAX)
DECLARE @IndexName sysname
DECLARE @TblSchemaName sysname
SELECT @TblSchemaName = QUOTENAME(@SchemaName) + '.'
+ QUOTENAME(@TableName)
SELECT @CreateSQL = ''
SELECT @IndexColsSQL = ''
SELECT @WithSQL = ''
SELECT @IncludeSQL = ''
SELECT @WhereSQL = ''
WHILE EXISTS ( SELECT 1
FROM @IDXTable
WHERE CreateScript IS NULL )
BEGIN
SELECT TOP 1
@SchemaID = Schema_ID ,
@TableID = Object_ID ,
@IndexID = Index_ID ,
@isPrimaryKey = IsPrimaryKey ,
@IndexName = IndexName ,
@IndexType = IndexType
FROM @IDXTable
WHERE CreateScript IS NULL
AND SchemaName = @SchemaName
AND TableName = @TableName
ORDER BY Index_ID
IF @isPrimaryKey = 1
BEGIN
SELECT @ExistsSQL = ' EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'''
+ @TblSchemaName + ''') AND name = N'''
+ @IndexName + ''')'
SELECT @DropSQL = ' ALTER TABLE ' + @TblSchemaName
+ ' DROP CONSTRAINT [' + @IndexName + ']'
END
ELSE
BEGIN
SELECT @ExistsSQL = ' EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'''
+ @TblSchemaName + ''') AND name = N'''
+ @IndexName + ''')'
SELECT @DropSQL = ' DROP INDEX [' + @IndexName + '] ON '
+ @TblSchemaName
+ CASE WHEN @IndexType IN ( 1, 2 )
THEN ' WITH ( ONLINE = OFF )'
ELSE ''
END
END
IF @IndexType IN ( 1, 2 )
BEGIN
SELECT @CreateSQL = CASE WHEN SI.is_primary_key = 1
THEN 'ALTER TABLE '
+ @TblSchemaName
+ ' ADD CONSTRAINT ['
+ @IndexName
+ '] PRIMARY KEY '
+ SI.type_desc
WHEN SI.type IN ( 1, 2 )
THEN ' CREATE '
+ CASE SI.is_unique
WHEN 1 THEN ' UNIQUE '
ELSE ''
END + SI.type_desc
+ ' INDEX '
+ QUOTENAME(SI.name)
+ ' ON ' + @TblSchemaName
END ,
@IndexColsSQL = ( SELECT SC.name + ' '
+ CASE SIC.is_descending_key
WHEN 0
THEN ' ASC '
ELSE 'DESC'
END + ','
FROM sys.index_columns SIC
JOIN sys.columns SC ON SIC.object_id = SC.object_id
AND SIC.column_id = SC.column_id
WHERE SIC.object_id = SI.object_id
AND SIC.index_id = SI.index_id
AND SIC.is_included_column = 0
ORDER BY SIC.key_ordinal
FOR
XML PATH('')
) ,
@WithSQL = ' WITH (PAD_INDEX = '
+ CASE SI.is_padded
WHEN 1 THEN 'ON'
ELSE 'OFF'
END + ',' + CHAR(13) + ' IGNORE_DUP_KEY = '
+ CASE SI.ignore_dup_key
WHEN 1 THEN 'ON'
ELSE 'OFF'
END + ',' + CHAR(13) + ' ALLOW_ROW_LOCKS = '
+ CASE SI.allow_row_locks
WHEN 1 THEN 'ON'
ELSE 'OFF'
END + ',' + CHAR(13) + ' ALLOW_PAGE_LOCKS = '
+ CASE SI.allow_page_locks
WHEN 1 THEN 'ON'
ELSE 'OFF'
END + ',' + CHAR(13)
+ CASE SI.type
WHEN 2
THEN 'SORT_IN_TEMPDB = ' + @SORT_IN_TEMPDB
+ ',DROP_EXISTING = ' + @DROP_EXISTING
+ ','
ELSE ''
END
+ CASE WHEN SI.fill_factor > 0
THEN ' FILLFACTOR = '
+ CONVERT(VARCHAR(3), SI.fill_factor)
+ ','
ELSE ''
END + ' STATISTICS_NORECOMPUTE = '
+ @STATISTICS_NORECOMPUTE + ', SORT_IN_TEMPDB = '
+ @SORT_IN_TEMPDB + ', ONLINE = ' + @ONLINE
+ ') ON ' + QUOTENAME(SFG.name) ,
@IncludeSQL = ( SELECT QUOTENAME(SC.name) + ','
FROM sys.index_columns SIC
JOIN sys.columns SC ON SIC.object_id = SC.object_id
AND SIC.column_id = SC.column_id
WHERE SIC.object_id = SI.object_id
AND SIC.index_id = SI.index_id
AND SIC.is_included_column = 1
ORDER BY SIC.key_ordinal
FOR
XML PATH('')
) ,
@WhereSQL = SI.filter_definition
FROM sys.indexes SI
JOIN sys.filegroups SFG ON SI.data_space_id = SFG.data_space_id
WHERE object_id = @TableID
AND index_id = @IndexID
SELECT @IndexColsSQL = '(' + SUBSTRING(@IndexColsSQL, 1,
LEN(@IndexColsSQL)
- 1) + ')'
IF LTRIM(RTRIM(@IncludeSQL)) <> ''
SELECT @IncludeSQL = ' INCLUDE ('
+ SUBSTRING(@IncludeSQL, 1,
LEN(@IncludeSQL) - 1) + ')'
IF LTRIM(RTRIM(@WhereSQL)) <> ''
SELECT @WhereSQL = ' WHERE (' + @WhereSQL + ')'
END
IF @IndexType = 3
BEGIN
SELECT @CreateSQL = ' CREATE '
+ CASE WHEN SI.using_xml_index_id IS NULL
THEN ' PRIMARY '
ELSE ''
END + SI.type_desc + ' INDEX '
+ QUOTENAME(SI.name) + ' ON ' + @TblSchemaName ,
@IndexColsSQL = ( SELECT SC.name + ' ' + ','
FROM sys.index_columns SIC
JOIN sys.columns SC ON SIC.object_id = SC.object_id
AND SIC.column_id = SC.column_id
WHERE SIC.object_id = SI.object_id
AND SIC.index_id = SI.index_id
AND SIC.is_included_column = 0
ORDER BY SIC.key_ordinal
FOR
XML PATH('')
) ,
@WithSQL = ' WITH (PAD_INDEX = '
+ CASE SI.is_padded
WHEN 1 THEN 'ON'
ELSE 'OFF'
END + ',' + CHAR(13) + ' ALLOW_ROW_LOCKS = '
+ CASE SI.allow_row_locks
WHEN 1 THEN 'ON'
ELSE 'OFF'
END + ',' + CHAR(13) + ' ALLOW_PAGE_LOCKS = '
+ CASE SI.allow_page_locks
WHEN 1 THEN 'ON'
ELSE 'OFF'
END + ',' + CHAR(13)
+ CASE SI.type
WHEN 2
THEN 'SORT_IN_TEMPDB = OFF,DROP_EXISTING = OFF,'
ELSE ''
END
+ CASE WHEN SI.fill_factor > 0
THEN ' FILLFACTOR = '
+ CONVERT(VARCHAR(3), SI.fill_factor)
+ ','
ELSE ''
END + 'SORT_IN_TEMPDB = OFF ' + ') ' ,
@IncludeSQL = ' USING XML INDEX ['
+ ( SELECT name
FROM sys.xml_indexes SIP
WHERE SIP.object_id = SI.object_id
AND SIP.index_id = SI.using_xml_index_id
) + '] FOR PATH '
FROM sys.xml_indexes SI
WHERE SI.object_id = @TableID
AND SI.index_id = @IndexID
SELECT @IndexColsSQL = '(' + SUBSTRING(@IndexColsSQL, 1,
LEN(@IndexColsSQL)
- 1) + ')'
END
IF @IndexType = 4
BEGIN
SELECT @CreateSQL = ' CREATE SPATIAL INDEX '
+ QUOTENAME(SI.name) + ' ON ' + @TblSchemaName ,
@IndexColsSQL = ( SELECT '('
+ QUOTENAME(SC.name)
+ ') '
FROM sys.index_columns SIC
JOIN sys.columns SC ON SIC.object_id = SC.object_id
AND SIC.column_id = SC.column_id
WHERE SIC.object_id = SI.object_id
AND SIC.index_id = SI.index_id
AND SIC.is_included_column = 0
) ,
@WithSQL = ' WITH ('
+ +' BOUNDING_BOX = ( XMIN = '
+ CONVERT(VARCHAR(25), SIT.bounding_box_xmin)
+ ', YMIN = '
+ CONVERT(VARCHAR(25), SIT.bounding_box_ymin) + ','
+ ' XMAX = '
+ CONVERT(VARCHAR(25), SIT.bounding_box_xmax)
+ ', YMAX = '
+ CONVERT(VARCHAR(25), SIT.bounding_box_ymax)
+ ' ), ' + CHAR(13) + ' CELLS_PER_OBJECT = '
+ CONVERT(VARCHAR(3), SIT.cells_per_object)
+ CHAR(13)
+ CASE WHEN SIT.level_1_grid IS NOT NULL
THEN ' , GRIDS = ( LEVEL_1 = '
+ SIT.level_1_grid_desc
+ ', LEVEL_2 = '
+ SIT.level_2_grid_desc
+ ', LEVEL_3 = '
+ SIT.level_3_grid_desc
+ ', LEVEL_4 = '
+ SIT.level_4_grid_desc + ' ) '
ELSE ''
END + ', PAD_INDEX = ' + CASE SI.is_padded
WHEN 1 THEN 'ON'
ELSE 'OFF'
END + ',' + CHAR(13)
+ ' ALLOW_ROW_LOCKS = ' + CASE SI.allow_row_locks
WHEN 1 THEN 'ON'
ELSE 'OFF'
END + ',' + CHAR(13)
+ ' ALLOW_PAGE_LOCKS = '
+ CASE SI.allow_page_locks
WHEN 1 THEN 'ON'
ELSE 'OFF'
END + ',' + CHAR(13)
+ CASE SI.type
WHEN 2
THEN 'SORT_IN_TEMPDB = OFF,DROP_EXISTING = OFF,'
ELSE ''
END
+ CASE WHEN SI.fill_factor > 0
THEN ' FILLFACTOR = '
+ CONVERT(VARCHAR(3), SI.fill_factor)
+ ','
ELSE ''
END + 'SORT_IN_TEMPDB = OFF ' + CHAR(13) + ') ' ,
@IncludeSQL = ' USING ' + SI.tessellation_scheme
FROM sys.spatial_indexes SI
JOIN sys.spatial_index_tessellations SIT ON SIT.index_id = SI.index_id
AND SIT.object_id = SI.object_id
WHERE SI.[object_id] = @TableID
AND SI.index_id = @IndexID
END
SELECT @CreateSQL = @CreateSQL + @IndexColsSQL
+ CASE WHEN @IndexColsSQL <> '' THEN CHAR(13)
ELSE ''
END + ISNULL(@IncludeSQL, '')
+ CASE WHEN @IncludeSQL <> '' THEN CHAR(13)
ELSE ''
END + ISNULL(@WhereSQL, '')
+ CASE WHEN @WhereSQL <> '' THEN CHAR(13)
ELSE ''
END + @WithSQL
UPDATE @IDXTable
SET CreateScript = @CreateSQL ,
DropScript = @DropSQL ,
ExistsScript = @ExistsSQL
WHERE Schema_ID = @SchemaID
AND Object_ID = @TableID
AND Index_ID = @IndexID
END
PRINT REPLICATE('-', 100)
PRINT '-- DROP Indexes'
PRINT REPLICATE('-', 100)
UPDATE @IDXTable
SET Processed = 0
WHERE SchemaName = @SchemaName
AND TableName = @TableName
WHILE EXISTS ( SELECT 1
FROM @IDXTable
WHERE ISNULL(Processed, 0) = 0
AND SchemaName = @SchemaName
AND TableName = @TableName )
BEGIN
SELECT @SQL = ''
SELECT TOP 1
@SchemaID = Schema_ID ,
@TableID = Object_ID ,
@IndexID = Index_ID ,
@SQL = 'IF ' + ExistsScript + CHAR(13) + DropScript
+ CHAR(13)
FROM @IDXTable
WHERE ISNULL(Processed, 0) = 0
AND SchemaName = @SchemaName
AND TableName = @TableName
ORDER BY IndexType DESC ,
Index_ID DESC
PRINT @SQL
UPDATE @IDXTable
SET Processed = 1
WHERE Schema_ID = @SchemaID
AND Object_ID = @TableID
AND Index_ID = @IndexID
END
PRINT REPLICATE('-', 100)
PRINT '-- Create Indexes'
PRINT REPLICATE('-', 100)
UPDATE @IDXTable
SET Processed = 0
WHERE SchemaName = @SchemaName
AND TableName = @TableName
WHILE EXISTS ( SELECT 1
FROM @IDXTable
WHERE ISNULL(Processed, 0) = 0
AND SchemaName = @SchemaName
AND TableName = @TableName )
BEGIN
SELECT @SQL = ''
SELECT TOP 1
@SchemaID = Schema_ID ,
@TableID = Object_ID ,
@IndexID = Index_ID ,
@SQL = 'IF NOT ' + ExistsScript + CHAR(13) + CreateScript
+ CHAR(13)
FROM @IDXTable
WHERE ISNULL(Processed, 0) = 0
AND SchemaName = @SchemaName
AND TableName = @TableName
-- make sure spatial are always last
ORDER BY ( ISNULL(NULLIF(IndexType, 4), -1) ) DESC ,
Index_ID DESC
PRINT @SQL
UPDATE @IDXTable
SET Processed = 1
WHERE Schema_ID = @SchemaID
AND Object_ID = @TableID
AND Index_ID = @IndexID
END
GO
--EXEC ScriptCreateDropIndexes_SP @TableName ='Personme',@SchemaName ='Person'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment