[ SQL Server ] Script – Multiple indexes for same columns

Publicado: abril 29, 2016 em Uncategorized
Tags:, ,

/*

CREATE TYPE TableParameter AS TABLE
(
TableName varchar(1000),
IndexName varchar(1000),
IndexType varchar(1000),
IndexId int,
ColumnId int,
ColumnName varchar(1000)
)

CREATE TYPE IndexParameter AS TABLE
(
TableName varchar(1000),
IndexName varchar(1000),
CountColumns int
)

CREATE TYPE TableEqualParameter AS TABLE
(
TableName varchar(1000),
IndexName varchar(1000),
IndexType varchar(1000),
IndexId int,
ColumnId int,
ColumnName varchar(1000),
TableName2 varchar(1000),
IndexName2 varchar(1000),
IndexType2 varchar(1000),
IndexId2 int,
ColumnId2 int,
ColumnName2 varchar(1000)
)
GO

DROP TYPE TableParameter;
DROP TYPE IndexParameter;
DROP TYPE TableEqualParameter;

*/

DECLARE @Index_table AS TableParameter;
DECLARE @Index_table_result AS TableParameter;
DECLARE @Index_count AS IndexParameter;
DECLARE @Index_table_equal AS TableEqualParameter;
DECLARE @TableName varchar(1000);
DECLARE @IndexName varchar(1000);
DECLARE @IndexType varchar(1000);
DECLARE @IndexId int;
DECLARE @ColumnId int;
DECLARE @ColumnName varchar(1000);
DECLARE @Count_Column int;
DECLARE @count int;
DECLARE @teste int;
DECLARE @TableNameDiff varchar(1000);
DECLARE @IndexNameDiff varchar(1000);
DECLARE @IndexTypeDiff varchar(1000);
DECLARE @IndexIdDiff int;
DECLARE @ColumnIdDiff int;
DECLARE @ColumnNameDiff varchar(1000);
DECLARE @Count_ColumnDiff int;

INSERT INTO @Index_table
SELECT
TableName = t.name,
IndexName = ind.name,
IndexType = ind.type,
IndexId = ind.index_id,
ColumnId = ic.index_column_id,
ColumnName = col.name
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
ind.is_primary_key = 0
AND ind.is_unique = 0
AND ind.is_unique_constraint = 0
AND t.is_ms_shipped = 0
and ind.is_disabled = 0
and ind.is_hypothetical = 0

INSERT INTO @Index_count
SELECT
TableName = t.name,
IndexName = ind.name,
COUNT(*) as ‘count_column’
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
ind.is_primary_key = 0
AND ind.is_unique = 0
AND ind.is_unique_constraint = 0
AND t.is_ms_shipped = 0
and ind.is_disabled = 0
group by t.name,
ind.name

DECLARE index_cursor CURSOR FOR
SELECT T.*,
c.CountColumns
from @Index_table T
INNER JOIN
@Index_count C ON t.tablename = c.tablename and t.indexname = c.indexname

OPEN index_cursor

FETCH NEXT FROM index_cursor
INTO @TableName,
@IndexName,
@IndexType,
@IndexId,
@ColumnId,
@ColumnName,
@Count_Column

WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE index_cursor_diff CURSOR FOR
SELECT t.*
FROM @Index_table t
INNER JOIN
@Index_count C ON t.tablename = c.tablename and t.indexname = c.indexname
WHERE t.TableName = @TableName
AND t.IndexId <> @IndexId
AND CountColumns <= @Count_Column

OPEN index_cursor_diff

FETCH NEXT FROM index_cursor_diff
INTO @TableNameDiff,
@IndexNameDiff,
@IndexTypeDiff,
@IndexIdDiff,
@ColumnIdDiff,
@ColumnNameDiff

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT @TableName
PRINT @IndexName
PRINT @TableNameDiff
PRINT @IndexNameDiff
PRINT ‘ ‘

INSERT INTO @Index_table_result (TableName,
ColumnName)
SELECT TableName,
ColumnName
FROM @Index_table
WHERE TableName = @TableName
AND IndexName = @IndexName
EXCEPT
SELECT TableName,
ColumnName
FROM @Index_table
WHERE TableName = @TableNameDiff
AND IndexName = @IndexNameDiff

SELECT @teste = COUNT(*) FROM @Index_table_result
PRINT @teste
PRINT @Count_Column
IF ((SELECT COUNT(*) as ‘qtd’ FROM @Index_table_result) = 0)
— OR (SELECT COUNT(*) as ‘qtd’ FROM @Index_table_result) <> @Count_Column
AND (select count(*) from @Index_table_equal where indexname = @indexnamediff and indexname2 = @indexname) = 0
BEGIN

INSERT INTO @Index_table_equal VALUES (@TableName,
@IndexName,
@IndexType,
@IndexId,
@ColumnId,
@ColumnName,
@TableNameDiff,
@IndexNameDiff,
@IndexTypeDiff,
@IndexIdDiff,
@ColumnIdDiff,
@ColumnNameDiff);

END

DELETE FROM @Index_table_result

FETCH NEXT FROM index_cursor_diff
INTO @TableNameDiff,
@IndexNameDiff,
@IndexTypeDiff,
@IndexIdDiff,
@ColumnIdDiff,
@ColumnNameDiff

END

CLOSE index_cursor_diff
DEALLOCATE index_cursor_diff
— Get the next index.
FETCH NEXT FROM index_cursor
INTO @TableName,
@IndexName,
@IndexType,
@IndexId,
@ColumnId,
@ColumnName,
@Count_Column

END

select tablename, IndexName, IndexName2, ColumnName from @Index_table_equal group by tablename, IndexName, IndexName2, ColumnName

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s