I have a situation in my SQL Server 2008.
I need to change a column type, but the indexes are preventing the changes. But because of the database is on several clients, I don't know how many indexes exists involving the column.
Is there any way of getting, programmatically speaking, all indexes that involve the column and drop them, and after the alter table statement recreate them automatically?
I've heard that disabling them can mess with the table because of the change of type.
I'm changing from tinyint to smallint type.
DISABLE all indexes on destination table
ALTER INDEX Indexname ON Table DISABLE
Then Alter datatype of a column
ALTER TABLE table
ALTER COLUMN columnname datatype
After that Enable Indexes
ALTER INDEX Indexname ON Table REBUILD
Also try this to know the all the indexes on table with column names:
SELECT OBJECT_SCHEMA_NAME(ind.object_id) AS SchemaName
, OBJECT_NAME(ind.object_id) AS ObjectName
, ind.name AS IndexName
, ind.is_primary_key AS IsPrimaryKey
, ind.is_unique AS IsUniqueIndex
, col.name AS ColumnName
, ic.is_included_column AS IsIncludedColumn
, ic.key_ordinal AS ColumnOrder
FROM sys.indexes ind
INNER JOIN sys.index_columns ic
ON ind.object_id = ic.object_id
AND ind.index_id = ic.index_id
INNER JOIN sys.columns col
ON ic.object_id = col.object_id
AND ic.column_id = col.column_id
INNER JOIN sys.tables t
ON ind.object_id = t.object_id
WHERE t.is_ms_shipped = 0
ORDER BY OBJECT_SCHEMA_NAME(ind.object_id) --SchemaName
, OBJECT_NAME(ind.object_id) --ObjectName
, ind.is_primary_key DESC
, ind.is_unique DESC
, ind.name --IndexName
, ic.key_ordinal
You can use below script which returns Index Name and Type for specified Table/Column.:
DECLARE @tableName SYSNAME
DECLARE @columnName SYSNAME
SET @tableName = 'Products'
SET @columnName = 'Name'
SELECT IDX.name, IDX.type_desc, IndexedColumn
FROM sys.tables TBL
INNER JOIN sys.indexes IDX ON TBL.object_id = IDX.object_id
LEFT JOIN sys.filegroups FG ON IDX.data_space_id = FG.data_space_id
CROSS APPLY
( SELECT COLS.Name
FROM sys.index_columns IXCL
INNER JOIN sys.columns COLS
ON IXCL.object_id = COLS.object_id
AND IXCL.column_id = COLS.column_id
WHERE IDX.object_id = IXCL.object_id
AND IDX.index_id = IXCL.index_id
AND COLS.name = @columnName
AND IDX.object_id = OBJECT_ID(@tableName)
) Indexed (IndexedColumn)
WHERE TBL.object_id = OBJECT_ID(@tableName)
Hope This Helps...
You can use the built-in tools to do this job. In SQL Server Management Studio, click "Tools" then "Options"
Expand the "SQL Server Object Explorer" set and Within that, click "Scripting".
Scroll down to the "Table And View Options" on the right hand side.
Find the record called "Script Indexes" and set it to "True", then click OK.
When you right click your table in the Object Explorer, you have options to "Script As..." selecting any of these options will now script out the indexes as well as the table itself and its keys. Copy the required scripts, or just run the whole thing depending on your need.
Lets assume basic case (column is not a part of any constraint, not an XML column having XML index over it, etc.), the following can be done:
- generate indexes descriptions as an XML using
select (...) from
<sys.indexes + other sys schema views> FOR XML ...
- put each XML as extended property of a table naming it e.g.
with a prefix 'IX_' ('IX_1', 'IX_2', etc...)
- drop indexes
- alter column
- gather all extended properties of a table having prefix 'IX_'
- recreate each index based on its XML description
Here is some example SQL of dropping and recreating an index safely:
IF(select object_id from sys.indexes where [name] = 'IDX_RecordSubscription' and object_id = OBJECT_ID('[SystemSetup].[RecordSubscription]')) IS NOT NULL
BEGIN
DROP INDEX [SystemSetup].[RecordSubscription].IDX_RecordSubscription
END
GO
CREATE UNIQUE INDEX
IDX_RecordSubscription
ON
[SystemSetup].[RecordSubscription]
(
Subscriber ASC,
MenuItem ASC,
RecordPrimaryKeyGuid ASC
)
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 = OFF
) ON [PRIMARY]
GO
Here is some C# code that pumps this out:
protected override string GetCreateIndexScript(string uniquePart, string indexName, string fullTableName, string columnsPart)
{
return
$"IF(select object_id from sys.indexes where [name] = '{indexName}' and object_id = OBJECT_ID('{fullTableName}')) IS NOT NULL \r\n" +
"BEGIN \r\n" +
$" DROP INDEX {fullTableName}.{indexName} \r\n " +
"END\r\n\r\n" +
"GO\r\n\r\n" +
$"CREATE {uniquePart} INDEX\r\n" +
$"\t{indexName}\r\n" +
"ON\r\n" +
$"\t{fullTableName}\r\n" +
"\t(\r\n" +
$"\t\t{columnsPart}\r\n" +
"\t)\r\n" +
"\tWITH\r\n" +
"\t(\r\n" +
"\t\tPAD_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 = OFF\r\n" +
"\t) ON [PRIMARY] ";
}
Here's some C# (that can be converted to SQL) to get the index schema:
const string selectStatement = "select " +
" SCHEMAs.name + '.' + tabs.name as OBJECT_ID, " +
" ind.name as INDEX_NAME, " +
" indcol.index_id AS INDEX_ID, " +
" indcol.key_ordinal AS COLUMN_ORDINAL, " +
" col.name AS COLUMN_NAME, " +
" ind.is_unique " +
"from " +
" sys.indexes ind " +
"inner join " +
" sys.index_columns indcol " +
"on " +
" ind.object_id = indcol.object_id and " +
" ind.index_id = indcol.index_id " +
"inner join " +
" sys.columns col " +
"on " +
" col.object_id = indcol.object_id and " +
" col.column_id = indcol.column_id " +
"inner join " +
" sys.tables tabs " +
"on " +
" tabs.object_id = ind.object_id " +
"inner join " +
" sys.schemas schemas " +
"on " +
" tabs.schema_id = schemas.schema_id " +
"where " +
" ind.type =2 and" +
" tabs.name <> 'sysdiagrams' " +
"order by " +
" tabs.object_id, " +
" indcol.index_id, " +
" indcol.key_ordinal ";
return DatabaseAdapter.Fill(selectStatement, null, null, null);
So, basically, you execute the last piece of code here, iterate through the results (indexes and columns) and call GetCreateIndexScript for each index that is returned. Then you can safely execute each of the statements that are created to drop and recreate the indexes.
This same approach could be used with TSQL, or another language.
