SQL Server Drops and Recreates Table Indexes

Question

I have a situation in my SQL Server 2008.

I need to change the type of a column, but indexes prevent changes. But since the database is on multiple clients, I don’t know how many indexes there are including the column.

Is there a way to get, programmatically, all the indexes that include a column and delete them, and after the alter table operator to recreate them automatically?

I heard that turning them off can ruin the table due to a type change.

I am switching from tinyint to smallint type.

Answer 1

DISABLE all indexes in the destination table

  ALTER INDEX Indexname ON Table DISABLE 

Then change the column data type

 ALTER TABLE table ALTER COLUMN columnname datatype 

After that Enable Indexes

 ALTER INDEX Indexname ON Table REBUILD 

Answer 2

Also try this to find out all indexes in a 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 

Answer 3

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 ...

Answer 4

You can use the built-in tools to complete this task. In SQL Server Management Studio, click Tools, then Options

Expand SQL Server Object Explorer and click Scripts inside it.

Scroll down to “Table and View Options” on the right.

Find the entry called Script Indexes and set it to True, then click OK.

When you right-click on your table in the Object Browser, you have the "Script How ..." options, when you select any of these parameters, the script will now display indexes, as well as the table itself and its keys. Copy the necessary scripts or just run it all, depending on your needs.

Answer 5

Let's look at the basic case (a column is not part of any constraint, not an XML column with an XML index above it, etc.), we can do the following:

• generates index descriptions as XML using select (...) from <sys.indexes + other sys schema views> FOR XML ...
• puts each XML as an extended table property, calling it, for example. with the prefix "IX_" ("IX_1", "IX_2", etc.)
• fall indices
• change column
• collect all extended table properties with the prefix "IX _"
• recreate each index based on its XML description

Answer 6

Here is an example SQL to dump and recreate the 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 the C # code that deflates this:

  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 are some C # (which 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 are executing the last piece of code here, repeating the results (indexes and columns) and calling GetCreateIndexScript for each index returned. You can then safely execute each of the created statements to reset and recreate indexes.

The same approach can be used with TSQL or another language.