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