Vraag Een lijst van alle indexen


Ik vraag me af wat de eenvoudigste manier om een ​​lijst weer te geven van alle indexen voor alle tabellen in een database is.

Moet ik bellen sp_helpindex voor elke tabel en sla de resultaten op in een tijdelijke tabel of is er een eenvoudigere manier?

Kan iemand uitleggen waarom beperkingen worden opgeslagen in sysobjects, maar zijn indexen dat niet?


18
2018-01-08 19:11


oorsprong


antwoorden:


Hier is een voorbeeld van het soort zoekopdracht dat u nodig hebt:

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
;

Deze is enigszins specifiek voor een bepaald doel (ik gebruik het in een kleine C # -app om dubbele indexen te vinden en de uitvoer te formatteren zodat deze daadwerkelijk door een mens leesbaar is). Maar je zou het gemakkelijk aan je behoeften kunnen aanpassen.


33
2018-01-09 14:30



Je zou kunnen verwijzen sysindexes

Een andere truc is om naar de tekst van sp_helpindex te kijken om te zien hoe deze informatie uit de onderliggende tabellen reconstrueert.

sp_helptext 'sp_helpindex'

Ik heb hier geen referentie voor, maar ik geloof dat beperkingen niet worden opgeslagen in sysobjects omdat ze een ander soort ding zijn; sysindexes bevat metagegevens over objecten in sysobjects.


6
2018-01-08 19:13



Als u meer informatie nodig heeft, is hier een aardig SQL-script dat ik van tijd tot tijd gebruik:

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

4
2018-01-08 19:22



Hier is een script dat SQL-instructies retourneert om alle indexen in een database opnieuw te maken.

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 

3
2018-02-21 18:48



De andere antwoorden zijn SQL Server-specifiek, met behulp van sysobjects of sys. * Catalogusweergaven - u kunt ook de ISO-standaard INFORMATION_SCHEMA-weergaven raadplegen.

Deze worden ondersteund door meerdere database-verkopers, b.v. SQL Server, MySQL, PostgreSQL en meer. Degene waar je waarschijnlijk naar op zoek bent, is:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

die een opsomming zal geven van de beperkingen van de unieke, primaire sleutel en externe sleutel - alles wat wordt omgezet in een index op SQL Server.


-1
2018-02-28 08:09