Reasoning

Ever had some indexes in your database that just do not make sense?

For example:

Sales_12052017,

okay you might say, now we know when it is created.

But there are extended events or traces to capture these kind of things,  and that is not what naming your indexes is for.

 

The script

This script will check your indexes for non default naming (IX_) and rename them like this:

IX_TableName_ColumnName1_Columname2_Columname3

The script will also cut off the name if the name is too long.

 

DECLARE  @schemaname nvarchar(200),
		 @tablename nvarchar(200), 
		 @indexname nvarchar(200), 
		 @colname nvarchar(200), 
		 @indexcolumnid int, 
		 @cmd nvarchar(max),
		 @length int;

DECLARE c CURSOR FOR 
SELECT  DISTINCT S.name, 
				 T.name, 
				 I.name 
FROM sys.indexes I 
INNER JOIN sys.index_columns IC 
ON I.object_id = IC.object_id 
AND I.index_id = IC.index_id 
INNER JOIN sys.columns C 
ON ic.object_id = C.object_id 
AND IC.column_id = C.column_id 
INNER JOIN sys.tables T 
ON I.object_id = T.object_id 
INNER JOIN sys.schemas S 
on T.schema_id = S.schema_id
WHERE 
	 I.is_unique_constraint = 0 
	 AND I.is_primary_key = 0
     AND T.is_ms_shipped = 0 
     AND I.is_unique = 0 
	 AND (I.name not like '%IX%' and I.name not like'%PK%')
ORDER BY 
     t.name, I.name
	
open c
fetch next from c into @schemaname, @tablename, @indexname;
while @@FETCH_STATUS = 0
BEGIN
	SET @cmd = 'exec sp_rename N'''+@schemaname+'.'+@tablename+'.'+@indexname+'' +CHAR(39);
	set @length = len(@cmd);
	if @length is null
	BEGIN
	set @length = 0
	END
	
	DECLARE d CURSOR FOR
	SELECT  ic.index_column_id, 
			C.name  
	FROM sys.indexes I 
	INNER JOIN sys.index_columns IC 
	ON I.object_id = IC.object_id 
	AND I.index_id = IC.index_id 
	INNER JOIN sys.columns C 
	ON ic.object_id = C.object_id 
	AND IC.column_id = C.column_id 
	INNER JOIN sys.tables T 
	ON I.object_id = T.object_id 
	INNER JOIN sys.schemas S 
	on T.schema_id = S.schema_id
	WHERE 
	I.is_unique_constraint = 0 
	AND I.is_primary_key = 0
    AND T.is_ms_shipped = 0 
    AND I.is_unique = 0 
	AND (I.name not like '%IX%' and I.name not like'%PK%')
	AND I.name = @indexname
	ORDER BY 
	index_column_id ASC;

	OPEN d
	FETCH NEXT FROM d INTO @indexcolumnid,@colname
	WHILE @@FETCH_STATUS = 0
	BEGIN

	IF @indexcolumnid = 1 
	BEGIN
		set @cmd += ' , N''IX_'+@tablename+'_'+@colname+ ''
	END

	else
	BEGIN
		set  @cmd += '_'+@colname
	END

	FETCH NEXT FROM d INTO @indexcolumnid,@colname
	END
	CLOSE d
	DEALLOCATE d
	set @cmd = LEFT(@cmd,128+@length) -- 128 is the maxlength for the index name + prefixes
	SET @cmd += ''', N''INDEX'';'
	PRINT @cmd   
fetch next from c into @schemaname, @tablename, @indexname;
END
CLOSE c;
DEALLOCATE c;