Index realign

This is not something that will be used very often, if ever by most people. But I have had occation where the indexes under a table are not correctly named. This results in the system dropping the indexes and rebuilding, which can take some time. It can also lead to other tables not being able to create new indexes, espcially for the recid index. The script below will identify indexes that are named incorrectly (table reference number being incorrect) and provide the SQL to rename the index.

SELECT
	o.name AS Table_Name,
	TableId.tableid as TableId,
	i.name AS Index_Name,
    substring(SUBSTRING(i.name, 3, 7), 1, PATINDEX('%[^0-9]%', SUBSTRING(i.name, 3, 7))-1) as OldTableId,
	SQLDictionary.tableid as TableId
    ,'EXEC sp_rename N''' + o.name + '.' + i.name + ''', N''I_' + cast(TableId.tableid as varchar(10)) + substring(i.name, 3 + len(substring(SUBSTRING(i.name, 3, 7), 1, PATINDEX('%[^0-9]%', SUBSTRING(i.name, 3, 7))-1)), 20) + ''', N''INDEX'';' as UpdateScript
	,'EXEC sp_rename N''' + o.name + '.' + i.name + ''', N''I_' + cast(TableId.recid as varchar(15)) + ''', N''INDEX'';' as UpdateScript2
FROM
	sys.indexes i INNER JOIN
	sys.objects o ON i.object_id = o.object_id INNER JOIN 
	sys.schemas sc ON o.schema_id = sc.schema_id INNER JOIN
	SQLDictionary TableId on (o.name = TableId.name and TableId.fieldid = 0) INNER JOIN
	SQLDictionary on (o.name = SQLDictionary.name and SQLDictionary.fieldid = 0)
where 
	i.name IS NOT NULL and 
	o.type = 'U' and 
	DB_NAME() = '<AX database name>'
	and cast(substring(SUBSTRING(i.name, 3, 7), 1, PATINDEX('%[^0-9]%', SUBSTRING(i.name, 3, 7))-1) as bigint) <> SQLDictionary.tableid
and len(i.name) >= 10

You need to add the AX database name to the code <AX database name>