sql server - indexar - Listado de todos los índices

indexar xml

Question

Me pregunto cuál es la forma más simple de listar todos los índices para todas las tablas en una base de datos.

¿Debo llamar a sp_helpindex para cada tabla y almacenar los resultados en una tabla temporal, o hay una manera más fácil?

¿Alguien puede explicar por qué las restricciones se almacenan en sysobjects pero los índices no?

Answer 1

Puede hacer referencia a los sysindexes

Otro truco es mirar el texto de sp_helpindex para ver cómo reconstruye la información de las tablas subyacentes.

sp_helptext ''sp_helpindex''

No tengo una referencia para esto, pero creo que las restricciones no se almacenan en los objetos del sistema porque son un tipo diferente de cosas; sysindexes contiene metadatos sobre objetos en sysobjects.

Answer 2

Si necesita más información, aquí hay un buen script SQL, que uso de vez en cuando:

DECLARE @TabName varchar(100)
CREATE TABLE #temp (
TabName varchar(200), IndexName varchar(200), IndexDescr varchar(200),
IndexKeys varchar(200), IndexSize int
)
DECLARE cur CURSOR FAST_FORWARD LOCAL FOR
SELECT name FROM sysobjects WHERE xtype = ''U''
OPEN cur
FETCH NEXT FROM cur INTO @TabName
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #temp (IndexName, IndexDescr, IndexKeys)
EXEC sp_helpindex @TabName
UPDATE #temp SET TabName = @TabName WHERE TabName IS NULL
FETCH NEXT FROM cur INTO @TabName
END
CLOSE cur
DEALLOCATE cur
DECLARE @ValueCoef int
SELECT @ValueCoef = low FROM Master.dbo.spt_values WHERE number = 1 AND type = N''E''
UPDATE #temp SET IndexSize =
((CAST(sysindexes.used AS bigint) * @ValueCoef)/1024)/1024
FROM sysobjects INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
INNER JOIN #temp T ON T.TabName = sysobjects.name AND T.IndexName = sysindexes.name
SELECT * FROM #temp
ORDER BY TabName, IndexName
DROP TABLE #temp

Answer 3

Aquí hay un ejemplo del tipo de consulta que necesita:

select
i.name as IndexName,
o.name as TableName,
ic.key_ordinal as ColumnOrder,
ic.is_included_column as IsIncluded,
co.[name] as ColumnName
from sys.indexes i
join sys.objects o on i.object_id = o.object_id
join sys.index_columns ic on ic.object_id = i.object_id
and ic.index_id = i.index_id
join sys.columns co on co.object_id = i.object_id
and co.column_id = ic.column_id
where i.[type] = 2
and i.is_unique = 0
and i.is_primary_key = 0
and o.[type] = ''U''
--and ic.is_included_column = 0
order by o.[name], i.[name], ic.is_included_column, ic.key_ordinal
;

Este es algo específico para un propósito determinado (lo uso en una pequeña aplicación C # para encontrar índices duplicados y formatear el resultado para que sea realmente legible por un humano). Pero podrías adaptarlo fácilmente a tus necesidades.

Answer 4

Las otras respuestas son específicas de SQL Server, utilizando sysobjects o sys. * Vistas de catálogo; también puede consultar las vistas ISO estándar INFORMATION_SCHEMA.

Estos son compatibles con múltiples proveedores de bases de datos, por ejemplo, SQL Server, MySQL, PostgreSQL y más. El que probablemente esté buscando es:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

que enumerará las restricciones exclusivas, de clave primaria y de clave externa, todo lo que se convierta en un índice en SQL Server.

Answer 5

Aquí hay un script que devolverá sentencias de SQL para recrear todos los índices en una base de datos.

SELECT '' CREATE '' +
CASE
WHEN I.is_unique = 1 THEN '' UNIQUE ''
ELSE ''''
END +
I.type_desc COLLATE DATABASE_DEFAULT + '' INDEX '' +
I.name + '' ON '' +
SCHEMA_NAME(T.schema_id) + ''.'' + T.name + '' ( '' +
KeyColumns + '' ) '' +
ISNULL('' INCLUDE ('' + IncludedColumns + '' ) '', '''') +
ISNULL('' WHERE '' + I.filter_definition, '''') + '' WITH ( '' +
CASE
WHEN I.is_padded = 1 THEN '' PAD_INDEX = ON ''
ELSE '' PAD_INDEX = OFF ''
END + '','' +
''FILLFACTOR = '' + CONVERT(
CHAR(5),
CASE
WHEN I.fill_factor = 0 THEN 100
ELSE I.fill_factor
END
) + '','' +
-- default value
''SORT_IN_TEMPDB = OFF '' + '','' +
CASE
WHEN I.ignore_dup_key = 1 THEN '' IGNORE_DUP_KEY = ON ''
ELSE '' IGNORE_DUP_KEY = OFF ''
END + '','' +
CASE
WHEN ST.no_recompute = 0 THEN '' STATISTICS_NORECOMPUTE = OFF ''
ELSE '' STATISTICS_NORECOMPUTE = ON ''
END + '','' +
'' ONLINE = OFF '' + '','' +
CASE
WHEN I.allow_row_locks = 1 THEN '' ALLOW_ROW_LOCKS = ON ''
ELSE '' ALLOW_ROW_LOCKS = OFF ''
END + '','' +
CASE
WHEN I.allow_page_locks = 1 THEN '' ALLOW_PAGE_LOCKS = ON ''
ELSE '' ALLOW_PAGE_LOCKS = OFF ''
END + '' ) ON ['' +
DS.name + '' ] '' + CHAR(13) + CHAR(10) + '' GO'' [CreateIndexScript]
FROM sys.indexes I
JOIN sys.tables T
ON T.object_id = I.object_id
JOIN sys.sysindexes SI
ON I.object_id = SI.id
AND I.index_id = SI.indid
JOIN (
SELECT *
FROM (
SELECT IC2.object_id,
IC2.index_id,
STUFF(
(
SELECT '' , '' + C.name + CASE
WHEN MAX(CONVERT(INT, IC1.is_descending_key))
= 1 THEN
'' DESC ''
ELSE
'' ASC ''
END
FROM sys.index_columns IC1
JOIN sys.columns C
ON C.object_id = IC1.object_id
AND C.column_id = IC1.column_id
AND IC1.is_included_column =
0
WHERE IC1.object_id = IC2.object_id
AND IC1.index_id = IC2.index_id
GROUP BY
IC1.object_id,
C.name,
index_id
ORDER BY
MAX(IC1.key_ordinal)
FOR XML PATH('''')
),
1,
2,
''''
) KeyColumns
FROM sys.index_columns IC2
--WHERE IC2.Object_id = object_id(''Person.Address'') --Comment for all tables
GROUP BY
IC2.object_id,
IC2.index_id
) tmp3
)tmp4
ON I.object_id = tmp4.object_id
AND I.Index_id = tmp4.index_id
JOIN sys.stats ST
ON ST.object_id = I.object_id
AND ST.stats_id = I.index_id
JOIN sys.data_spaces DS
ON I.data_space_id = DS.data_space_id
JOIN sys.filegroups FG
ON I.data_space_id = FG.data_space_id
LEFT JOIN (
SELECT *
FROM (
SELECT IC2.object_id,
IC2.index_id,
STUFF(
(
SELECT '' , '' + C.name
FROM sys.index_columns IC1
JOIN sys.columns C
ON C.object_id = IC1.object_id
AND C.column_id = IC1.column_id
AND IC1.is_included_column =
1
WHERE IC1.object_id = IC2.object_id
AND IC1.index_id = IC2.index_id
GROUP BY
IC1.object_id,
C.name,
index_id
FOR XML PATH('''')
),
1,
2,
''''
) IncludedColumns
FROM sys.index_columns IC2
--WHERE IC2.Object_id = object_id(''Person.Address'') --Comment for all tables
GROUP BY
IC2.object_id,
IC2.index_id
) tmp1
WHERE IncludedColumns IS NOT NULL
) tmp2
ON tmp2.object_id = I.object_id
AND tmp2.index_id = I.index_id
WHERE I.is_primary_key = 0
AND I.is_unique_constraint = 0
--AND T.name NOT LIKE ''mt_%''
--AND I.name NOT LIKE ''mt_%''
--AND I.Object_id = object_id(''Person.Address'') --Comment for all tables
--AND I.name = ''IX_Address_PostalCode'' --comment for all indexes