感谢大家的评论。经过一些研究,我能够确定如何使用@mwigdahl提到的确切表动态地完成这项任务。请查看下面的代码:
DECLARE @indexName nvarchar(254),
@tableName nvarchar(254),
@indexType nvarchar(254),
@columnName nvarchar(254),
@isPadded integer,
@ignore_dup_key integer,
@allow_row_locks integer,
@allow_page_locks integer,
@fillFactor integer
SELECT
@indexName = i.name,
@tableName = o.name,
@indexType = i.type_desc,
@columnName = co.[name],
@isPadded = i.is_padded,
@ignore_dup_key = i.[ignore_dup_key],
@allow_row_locks = i.[allow_row_locks],
@allow_page_locks = i.[allow_page_locks],
@fillFactor = i.fill_factor
FROM sys.indexes AS i
INNER JOIN sys.objects AS o ON i.object_id = o.object_id
INNER JOIN sys.index_columns ic on ic.object_id = i.object_id AND ic.index_id = i.index_id
INNER JOIN sys.columns co on co.object_id = i.object_id AND co.column_id = ic.column_id
WHERE
i.[type] = 2 AND
o.[type] = 'U'
DECLARE @sql varchar(max)
SET @sql = 'DROP INDEX [' + @indexName + '] ON [' + @tableName + ']'
EXEC (@sql)
PRINT 'Dropped Index'
DECLARE @pad_index nvarchar(3),
@ignore_dup nvarchar(3),
@row_locks nvarchar(3),
@page_locks nvarchar(3)
IF @isPadded = 0 SET @pad_index = 'OFF'
ELSE SET @pad_index = 'ON'
IF @ignore_dup_key = 0 SET @ignore_dup = 'OFF'
ELSE SET @ignore_dup = 'ON'
IF @allow_row_locks = 0 SET @row_locks = 'OFF'
ELSE SET @row_locks = 'ON'
IF @allow_page_locks = 0 SET @page_locks = 'OFF'
ELSE SET @page_locks = 'ON'
SET @sql = 'CREATE ' + @indexType + ' INDEX [' + @indexName + '] ON [' + @tableName + ']' +
'(' +
'[' + @columnName + '] ASC' +
') WITH (' +
'PAD_INDEX = ' + @pad_index + ',' +
'STATISTICS_NORECOMPUTE = OFF,' +
'SORT_IN_TEMPDB = OFF,' +
'IGNORE_DUP_KEY = ' + @ignore_dup + ',' +
'DROP_EXISTING = OFF,' +
'ONLINE = OFF,' +
'ALLOW_ROW_LOCKS = ' + @row_locks + ',' +
'ALLOW_PAGE_LOCKS = ' + @page_locks + ',' +
'FILLFACTOR = ' + CONVERT(nvarchar(100),@fillFactor) + ')' +
'ON [PRIMARY]'
PRINT @sql
EXEC (@sql)
PRINT 'Created Index'
我在SSMS中使用了
Script Index As
函数来查看索引在T-SQL中的创建方式。
然后,我查询了
sys.indexes
和
sys.index_columns
表,以确定哪些属性可用。
代码显示了针对这些表的查询,将这些属性保存到变量中,然后删除并重新创建索引。这些表中有一些选项是不可用的,我必须硬编码它们。是否有人知道这些选项是否在系统表中可用?
希望这能帮助未来的某个人。
sys.indexes
、sys.columns
、sys.index_columns
和其他系统视图来确定附加到要修改的列的索引以及它们的构造方式。然后,您可以通过编程方式删除它们,进行列修改,然后重新创建索引。这些都不是简单的操作;代码最终会变得相当复杂,但我们在工作中使用类似的系统来自动化模式更改,效果非常好。 - mwigdahl