Liste de toutes les colonnes index & index dans la base de données SQL Server
Comment puis-je obtenir une liste de toutes les colonnes index & index dans SQL Server 2005+? Le plus proche que j'ai pu obtenir est:
select s.name, t.name, i.name, c.name from sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
inner join sys.indexes i on i.object_id = t.object_id
inner join sys.index_columns ic on ic.object_id = t.object_id
inner join sys.columns c on c.object_id = t.object_id and
ic.column_id = c.column_id
where i.index_id > 0
and i.type in (1, 2) -- clustered & nonclustered only
and i.is_primary_key = 0 -- do not include PK indexes
and i.is_unique_constraint = 0 -- do not include UQ
and i.is_disabled = 0
and i.is_hypothetical = 0
and ic.key_ordinal > 0
order by ic.key_ordinal
Qui n'est pas exactement ce que je veux.
Ce que je veux, c'est lister tous les index définis par l'utilisateur, ( qui signifie pas d'index supportant des contraintes uniques et des clés primaires ) avec toutes les colonnes (ordonnées par la façon dont elles apparaissent dans la définition de l'index) plus autant de métadonnées que possible.
26 réponses
il y a deux "sys" vues de catalogue que vous pouvez consulter:
select * from sys.indexes
select * from sys.index_columns
ceux-ci vous donneront à peu près n'importe quelle information que vous pourriez éventuellement vouloir sur les indices et leurs colonnes.
EDIT: cette requête devient assez proche de ce que vous cherchez:
SELECT
TableName = t.name,
IndexName = ind.name,
IndexId = ind.index_id,
ColumnId = ic.index_column_id,
ColumnName = col.name,
ind.*,
ic.*,
col.*
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
ORDER BY
t.name, ind.name, ind.index_id, ic.index_column_id;
vous pouvez utiliser le sp_helpindex
pour voir tous les index d'une table.
EXEC sys.sp_helpindex @objname = N'User' -- nvarchar(77)
Et pour tous les indices, vous pouvez parcourir sys.objects
pour obtenir tous les indices pour chaque table.
rien de ce qui précède n'a fait le travail pour moi, mais cela fait:
-- KDF9's concise index list for SQL Server 2005+ (see below for 2000)
-- includes schemas and primary keys, in easy to read format
-- with unique, clustered, and all ascending/descendings in a single column
-- Needs simple manual add or delete to change maximum number of key columns
-- but is easy to understand and modify, with no UDFs or complex logic
--
SELECT
schema_name(schema_id) as SchemaName, OBJECT_NAME(si.object_id) as TableName, si.name as IndexName,
(CASE is_primary_key WHEN 1 THEN 'PK' ELSE '' END) as PK,
(CASE is_unique WHEN 1 THEN '1' ELSE '0' END)+' '+
(CASE si.type WHEN 1 THEN 'C' WHEN 3 THEN 'X' ELSE 'B' END)+' '+ -- B=basic, C=Clustered, X=XML
(CASE INDEXKEY_PROPERTY(si.object_id,index_id,1,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+
(CASE INDEXKEY_PROPERTY(si.object_id,index_id,2,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+
(CASE INDEXKEY_PROPERTY(si.object_id,index_id,3,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+
(CASE INDEXKEY_PROPERTY(si.object_id,index_id,4,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+
(CASE INDEXKEY_PROPERTY(si.object_id,index_id,5,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+
(CASE INDEXKEY_PROPERTY(si.object_id,index_id,6,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+
'' as 'Type',
INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,1) as Key1,
INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,2) as Key2,
INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,3) as Key3,
INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,4) as Key4,
INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,5) as Key5,
INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,6) as Key6
FROM sys.indexes as si
LEFT JOIN sys.objects as so on so.object_id=si.object_id
WHERE index_id>0 -- omit the default heap
and OBJECTPROPERTY(si.object_id,'IsMsShipped')=0 -- omit system tables
and not (schema_name(schema_id)='dbo' and OBJECT_NAME(si.object_id)='sysdiagrams') -- omit sysdiagrams
ORDER BY SchemaName,TableName,IndexName
-------------------------------------------------------------------
-- or to generate creation scripts put a simple wrapper around that
SELECT SchemaName, TableName, IndexName,
(CASE pk
WHEN 'PK' THEN 'ALTER '+
'TABLE '+SchemaName+'.'+TableName+' ADD CONSTRAINT '+IndexName+' PRIMARY KEY'+
(CASE substring(Type,3,1) WHEN 'C' THEN ' CLUSTERED' ELSE '' END)
ELSE 'CREATE '+
(CASE substring(Type,1,1) WHEN '1' THEN 'UNIQUE ' ELSE '' END)+
(CASE substring(Type,3,1) WHEN 'C' THEN 'CLUSTERED ' ELSE '' END)+
'INDEX '+IndexName+' ON '+SchemaName+'.'+TableName
END)+
' ('+
(CASE WHEN Key1 is null THEN '' ELSE Key1+(CASE substring(Type,4+1,1) WHEN 'D' THEN ' DESC' ELSE '' END) END)+
(CASE WHEN Key2 is null THEN '' ELSE ', '+Key2+(CASE substring(Type,4+2,1) WHEN 'D' THEN ' DESC' ELSE '' END) END)+
(CASE WHEN Key3 is null THEN '' ELSE ', '+Key3+(CASE substring(Type,4+3,1) WHEN 'D' THEN ' DESC' ELSE '' END) END)+
(CASE WHEN Key4 is null THEN '' ELSE ', '+Key4+(CASE substring(Type,4+4,1) WHEN 'D' THEN ' DESC' ELSE '' END) END)+
(CASE WHEN Key5 is null THEN '' ELSE ', '+Key5+(CASE substring(Type,4+5,1) WHEN 'D' THEN ' DESC' ELSE '' END) END)+
(CASE WHEN Key6 is null THEN '' ELSE ', '+Key6+(CASE substring(Type,4+6,1) WHEN 'D' THEN ' DESC' ELSE '' END) END)+
')' as CreateIndex
FROM (
...
...listing SQL same as above minus the ORDER BY...
...
) as indexes
ORDER BY SchemaName,TableName,IndexName
----------------------------------------------------------
-- For SQL Server 2000 the following should work
-- change table names to sysindexes and sysobjects (no dots)
-- change object_id => id, index_id => indid,
-- change is_primary_key => (select count(constid) from sysconstraints as sc where sc.id=si.id and sc.status&15=1)
-- change is_unique => INDEXPROPERTY(si.id,si.name,'IsUnique')
-- change si.type => INDEXPROPERTY(si.id,si.name,'IsClustered')
-- remove all references to schemas including schema name qualifiers, and the XML type
-- add select where indid<255 and si.status&64=0 (to omit the text/image index and autostats)
si vos noms comprennent des espaces, ajoutez des crochets autour d'eux dans les scripts de création.
quand la dernière colonne est nulle, vous savez qu'il n'en manque aucune.
filtrer les clés primaires etc comme dans la requête originale est trivial.
NOTE: faites attention avec cette solution car elle ne distingue pas les indexés et les indexés. des colonnes incluses.
-- court et doux:
SELECT OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema],
T.[name] AS [table_name], I.[name] AS [index_name], AC.[name] AS [column_name],
I.[type_desc], I.[is_unique], I.[data_space_id], I.[ignore_dup_key], I.[is_primary_key],
I.[is_unique_constraint], I.[fill_factor], I.[is_padded], I.[is_disabled], I.[is_hypothetical],
I.[allow_row_locks], I.[allow_page_locks], IC.[is_descending_key], IC.[is_included_column]
FROM sys.[tables] AS T
INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]
INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id]
WHERE T.[is_ms_shipped] = 0 AND I.[type_desc] <> 'HEAP'
ORDER BY T.[name], I.[index_id], IC.[key_ordinal]
Hé les gars, Je ne suis pas passé par là mais j'ai eu ce que je voulais dans la requête Postée par l'auteur original.
Je l'ai utilisé (sans conditions / filtres) pour mes besoins mais il a donné des résultats incorrects
le problème principal était les résultats obtenir produit croisé sans condition de jointure sur index_id
SELECT S.NAME SCHEMA_NAME,T.NAME TABLE_NAME,I.NAME INDEX_NAME,C.NAME COLUMN_NAME
FROM SYS.TABLES T
INNER JOIN SYS.SCHEMAS S
ON T.SCHEMA_ID = S.SCHEMA_ID
INNER JOIN SYS.INDEXES I
ON I.OBJECT_ID = T.OBJECT_ID
INNER JOIN SYS.INDEX_COLUMNS IC
ON IC.OBJECT_ID = T.OBJECT_ID
INNER JOIN SYS.COLUMNS C
ON C.OBJECT_ID = T.OBJECT_ID
**AND IC.INDEX_ID = I.INDEX_ID**
AND IC.COLUMN_ID = C.COLUMN_ID
WHERE 1=1
ORDER BY I.NAME,I.INDEX_ID,IC.KEY_ORDINAL
ce qui suit donne ce qui est similaire à sp_helpindex tablename
select T.name as TableName, I.name as IndexName, AC.Name as ColumnName, I.type_desc as IndexType
from sys.tables as T inner join sys.indexes as I on T.[object_id] = I.[object_id]
inner join sys.index_columns as IC on IC.[object_id] = I.[object_id] and IC.[index_id] = I.[index_id]
inner join sys.all_columns as AC on IC.[object_id] = AC.[object_id] and IC.[column_id] = AC.[column_id]
order by T.name, I.name
j'ai eu besoin d'obtenir des index particuliers, leurs colonnes d'index et leurs colonnes incluses aussi bien. Voici la requête que j'ai utilisée:
SELECT INX.[name] AS [Index Name]
,TBL.[name] AS [Table Name]
,DS1.[IndexColumnsNames]
,DS2.[IncludedColumnsNames]
FROM [sys].[indexes] INX
INNER JOIN [sys].[tables] TBL
ON INX.[object_id] = TBL.[object_id]
CROSS APPLY
(
SELECT STUFF
(
(
SELECT ' [' + CLS.[name] + ']'
FROM [sys].[index_columns] INXCLS
INNER JOIN [sys].[columns] CLS
ON INXCLS.[object_id] = CLS.[object_id]
AND INXCLS.[column_id] = CLS.[column_id]
WHERE INX.[object_id] = INXCLS.[object_id]
AND INX.[index_id] = INXCLS.[index_id]
AND INXCLS.[is_included_column] = 0
FOR XML PATH('')
)
,1
,1
,''
)
) DS1 ([IndexColumnsNames])
CROSS APPLY
(
SELECT STUFF
(
(
SELECT ' [' + CLS.[name] + ']'
FROM [sys].[index_columns] INXCLS
INNER JOIN [sys].[columns] CLS
ON INXCLS.[object_id] = CLS.[object_id]
AND INXCLS.[column_id] = CLS.[column_id]
WHERE INX.[object_id] = INXCLS.[object_id]
AND INX.[index_id] = INXCLS.[index_id]
AND INXCLS.[is_included_column] = 1
FOR XML PATH('')
)
,1
,1
,''
)
) DS2 ([IncludedColumnsNames])
cela va fonctionner:
DECLARE @IndexInfo TABLE (index_name varchar(250)
,index_description varchar(250)
,index_keys varchar(250)
)
INSERT INTO @IndexInfo
exec sp_msforeachtable 'sp_helpindex ''?'''
select * from @IndexInfo
cela ne renvoie pas le nom de la table et vous obtiendrez des avertissements pour toutes les tables sans index, si c'est un problème, vous pouvez créer une boucle sur les tables qui ont des index comme ceci:
DECLARE @IndexInfoTemp TABLE (index_name varchar(250)
,index_description varchar(250)
,index_keys varchar(250)
)
DECLARE @IndexInfo TABLE (table_name sysname
,index_name varchar(250)
,index_description varchar(250)
,index_keys varchar(250)
)
DECLARE @Tables Table (RowID int not null identity(1,1)
,TableName sysname
)
DECLARE @MaxRow int
DECLARE @CurrentRow int
DECLARE @CurrentTable sysname
INSERT INTO @Tables
SELECT
DISTINCT t.name
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
WHERE i.Name IS NOT NULL
SELECT @MaxRow=@@ROWCOUNT,@CurrentRow=1
WHILE @CurrentRow<=@MaxRow
BEGIN
SELECT @CurrentTable=TableName FROM @Tables WHERE RowID=@CurrentRow
INSERT INTO @IndexInfoTemp
exec sp_helpindex @CurrentTable
INSERT INTO @IndexInfo
(table_name , index_name , index_description , index_keys)
SELECT
@CurrentTable , index_name , index_description , index_keys
FROM @IndexInfoTemp
DELETE FROM @IndexInfoTemp
SET @CurrentRow=@CurrentRow+1
END --WHILE
SELECT * from @IndexInfo
MODIFIER
si vous voulez, vous pouvez filtrer les données, voici quelques exemples (ces travaux pour l'une ou l'autre méthode):
SELECT * FROM @IndexInfo WHERE index_description NOT LIKE '%primary key%'
SELECT * FROM @IndexInfo WHERE index_description NOT LIKE '%nonclustered%' AND index_description LIKE '%clustered%'
SELECT * FROM @IndexInfo WHERE index_description LIKE '%unique%'
with connect(schema_name,table_name,index_name,index_column_id,column_name) as
( select s.name schema_name, t.name table_name, i.name index_name, index_column_id, cast(c.name as varchar(max)) column_name
from sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
inner join sys.indexes i on i.object_id = t.object_id
inner join sys.index_columns ic on ic.object_id = t.object_id and ic.index_id=i.index_id
inner join sys.columns c on c.object_id = t.object_id and
ic.column_id = c.column_id
where index_column_id=1
union all
select s.name schema_name, t.name table_name, i.name index_name, ic.index_column_id, cast(connect.column_name + ',' + c.name as varchar(max)) column_name
from sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
inner join sys.indexes i on i.object_id = t.object_id
inner join sys.index_columns ic on ic.object_id = t.object_id and ic.index_id=i.index_id
inner join sys.columns c on c.object_id = t.object_id and
ic.column_id = c.column_id join connect on
connect.index_column_id+1 = ic.index_column_id
and connect.schema_name = s.name
and connect.table_name = t.name
and connect.index_name = i.name)
select connect.schema_name,connect.table_name,connect.index_name,connect.column_name
from connect join (select schema_name,table_name,index_name,MAX(index_column_id) index_column_id
from connect group by schema_name,table_name,index_name) mx
on connect.schema_name = mx.schema_name
and connect.table_name = mx.table_name
and connect.index_name = mx.index_name
and connect.index_column_id = mx.index_column_id
order by 1,2,3
les travaux suivants sur SQL Server 2014/2016 ainsi que toute base de données Microsoft Azure SQL.
produit un jeu complet de résultats qui est facilement exportable à Notepad / Excel pour le tranchage et le découpage et comprend
- Nom De La Table
- Nom Index
- Description De L'Index
- colonnes indexées - dans L'ordre
- Colonnes Incluses - En ordre
SELECT '[' + s.NAME + '].[' + o.NAME + ']' AS 'table_name'
,+ i.NAME AS 'index_name'
,LOWER(i.type_desc) + CASE
WHEN i.is_unique = 1
THEN ', unique'
ELSE ''
END + CASE
WHEN i.is_primary_key = 1
THEN ', primary key'
ELSE ''
END AS 'index_description'
,STUFF((
SELECT ', [' + sc.NAME + ']' AS "text()"
FROM syscolumns AS sc
INNER JOIN sys.index_columns AS ic ON ic.object_id = sc.id
AND ic.column_id = sc.colid
WHERE sc.id = so.object_id
AND ic.index_id = i1.indid
AND ic.is_included_column = 0
ORDER BY key_ordinal
FOR XML PATH('')
), 1, 2, '') AS 'indexed_columns'
,STUFF((
SELECT ', [' + sc.NAME + ']' AS "text()"
FROM syscolumns AS sc
INNER JOIN sys.index_columns AS ic ON ic.object_id = sc.id
AND ic.column_id = sc.colid
WHERE sc.id = so.object_id
AND ic.index_id = i1.indid
AND ic.is_included_column = 1
FOR XML PATH('')
), 1, 2, '') AS 'included_columns'
FROM sysindexes AS i1
INNER JOIN sys.indexes AS i ON i.object_id = i1.id
AND i.index_id = i1.indid
INNER JOIN sysobjects AS o ON o.id = i1.id
INNER JOIN sys.objects AS so ON so.object_id = o.id
AND is_ms_shipped = 0
INNER JOIN sys.schemas AS s ON s.schema_id = so.schema_id
WHERE so.type = 'U'
AND i1.indid < 255
AND i1.STATUS & 64 = 0 --index with duplicates
AND i1.STATUS & 8388608 = 0 --auto created index
AND i1.STATUS & 16777216 = 0 --stats no recompute
AND i.type_desc <> 'heap'
AND so.NAME <> 'sysdiagrams'
ORDER BY table_name
,index_name;
C'est une façon de reculer dans l'index. Vous pouvez utiliser SHOWCONTIG pour évaluer la fragmentation. Il dressera la liste de tous les index de la base de données ou du tableau, ainsi que des statistiques. Je tiens à souligner que sur une base de données importante, cela peut être de longue durée. Pour moi, l'un des avantages de cette approche est que vous n'avez pas à être un administrateur de l'utiliser.
--afficher les informations de fragmentation sur tous les index dans une base de données
SET NOCOUNT ON
USE pubs
DBCC SHOWCONTIG WITH ALL_INDEXES
GO
...tour NOCOUNT back OFF lorsque tout est terminé
--afficher les informations de fragmentation sur tous les index sur une table
SET NOCOUNT ON
USE pubs
DBCC SHOWCONTIG (authors) WITH ALL_INDEXES
GO
--fragmentation de l'information sur un index spécifique
SET NOCOUNT ON
USE pubs
DBCC SHOWCONTIG (authors,aunmind)
GO
puis-je hasarder une autre réponse à cette question saturée?
il s'agit d'une refonte libérale de @marc_s answer, mélangée avec quelques trucs de @Tim Ford, avec l'objectif d'avoir un peu d'un résultat plus propre et plus simple ensemble et l'affichage final et la commande pour mon besoin actuel.
SELECT
OBJECT_SCHEMA_NAME(t.[object_id],DB_ID()) AS [Schema],
t.[name] AS [TableName],
ind.[name] AS [IndexName],
col.[name] AS [ColumnName],
ic.column_id AS [ColumnId],
ind.[type_desc] AS [IndexTypeDesc],
col.is_identity AS [IsIdentity],
ind.[is_unique] AS [IsUnique],
ind.[is_primary_key] AS [IsPrimaryKey],
ic.[is_descending_key] AS [IsDescendingKey],
ic.[is_included_column] AS [IsIncludedColumn]
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
t.is_ms_shipped = 0
--ind.is_primary_key = 1 -- include or not pks, etc
--AND ind.is_unique = 0
--AND ind.is_unique_constraint = 0
ORDER BY
[Schema],
TableName,
IndexName,
[ColumnId],
ColumnName
basé sur le code de Tim Ford, c'est la bonne réponse:
select tab.[name] as [table_name],
idx.[name] as [index_name],
allc.[name] as [column_name],
idx.[type_desc],
idx.[is_unique],
idx.[data_space_id],
idx.[ignore_dup_key],
idx.[is_primary_key],
idx.[is_unique_constraint],
idx.[fill_factor],
idx.[is_padded],
idx.[is_disabled],
idx.[is_hypothetical],
idx.[allow_row_locks],
idx.[allow_page_locks],
idxc.[is_descending_key],
idxc.[is_included_column],
idxc.[index_column_id]
from sys.[tables] as tab
inner join sys.[indexes] idx on tab.[object_id] = idx.[object_id]
inner join sys.[index_columns] idxc on idx.[object_id] = idxc.[object_id] and idx.[index_id] = idxc.[index_id]
inner join sys.[all_columns] allc on tab.[object_id] = allc.[object_id] and idxc.[column_id] = allc.[column_id]
where tab.[name] Like '%table_name%'
and idx.[name] Like '%index_name%'
order by tab.[name], idx.[index_id], idxc.[index_column_id]
j'ai trouvé celui-ci, qui me donne la vue d'ensemble exacte dont j'ai besoin. Ce qui est aide est que vous obtenez une ligne par index dans l'index des colonnes sont regroupées.
select
o.name as ObjectName,
i.name as IndexName,
i.is_primary_key as [PrimaryKey],
SUBSTRING(i.[type_desc],0,6) as IndexType,
i.is_unique as [Unique],
Columns.[Normal] as IndexColumns,
Columns.[Included] as IncludedColumns
from sys.indexes i
join sys.objects o on i.object_id = o.object_id
cross apply
(
select
substring
(
(
select ', ' + co.[name]
from sys.index_columns ic
join sys.columns co on co.object_id = i.object_id and co.column_id = ic.column_id
where ic.object_id = i.object_id and ic.index_id = i.index_id and ic.is_included_column = 0
order by ic.key_ordinal
for xml path('')
)
, 3
, 10000
) as [Normal]
, substring
(
(
select ', ' + co.[name]
from sys.index_columns ic
join sys.columns co on co.object_id = i.object_id and co.column_id = ic.column_id
where ic.object_id = i.object_id and ic.index_id = i.index_id and ic.is_included_column = 1
order by ic.key_ordinal
for xml path('')
)
, 3
, 10000
) as [Included]
) Columns
where o.[type] = 'U' --USER_TABLE
order by o.[name], i.[name], i.is_primary_key desc
basé sur la réponse acceptée et deux autres questions 1 , 2 j'ai rassemblé la requête suivante:
SELECT
sys.indexes.name AS IndexName,
sys.tables.name AS TableName,
REPLACE((
SELECT sys.columns.name + CASE WHEN is_descending_key = 1 THEN ' DESC' ELSE '' END AS [data()]
FROM sys.index_columns
INNER JOIN sys.columns ON sys.index_columns.object_id = sys.columns.object_id AND sys.index_columns.column_id = sys.columns.column_id
WHERE sys.index_columns.object_id = sys.indexes.object_id AND sys.index_columns.index_id = sys.indexes.index_id AND sys.index_columns.is_included_column = 0
ORDER BY sys.index_columns.key_ordinal
FOR XML PATH('')
), ' ', ', ') AS KeyColumns,
REPLACE((
SELECT sys.columns.name AS [data()]
FROM sys.index_columns
INNER JOIN sys.columns ON sys.index_columns.object_id = sys.columns.object_id AND sys.index_columns.column_id = sys.columns.column_id
WHERE sys.index_columns.object_id = sys.indexes.object_id AND sys.index_columns.index_id = sys.indexes.index_id AND sys.index_columns.is_included_column = 1
ORDER BY sys.index_columns.index_column_id
FOR XML PATH('')
), ' ', ', ') AS IncludedColumns,
sys.dm_db_index_usage_stats.user_updates,
sys.dm_db_index_usage_stats.user_seeks,
sys.dm_db_index_usage_stats.user_scans,
sys.dm_db_index_usage_stats.user_lookups,
sys.dm_db_index_usage_stats.user_seeks + sys.dm_db_index_usage_stats.user_scans + sys.dm_db_index_usage_stats.user_lookups AS total_usage
FROM sys.indexes
LEFT JOIN sys.tables ON sys.indexes.object_id = sys.tables.object_id
LEFT JOIN sys.dm_db_index_usage_stats ON sys.indexes.object_id = sys.dm_db_index_usage_stats.object_id AND sys.indexes.index_id = sys.dm_db_index_usage_stats.index_id
WHERE sys.indexes.type <> 0 AND sys.tables.is_ms_shipped = 0
cette requête renvoie des résultats tels que ci-dessous qui montre la liste des index, leurs colonnes et leur utilisation. Très utile pour déterminer quel indice donne de meilleurs résultats que les autres:
puisque votre profil indique que vous utilisez .NET vous pourrait utiliser des objets gérés par serveur (Omu) de façon programmatique... autrement, une des réponses ci-dessus sont fantastiques.
la solution ci-dessus est élégante, mais selon MS, INDEXKEY_PROPERTY est déprécié. Voir: http://msdn.microsoft.com/en-us/library/ms186773.aspx
Oracle
select CONNECYBY.SCHEMA_NAME,CONNECYBY.TABLE_NAME,CONNECYBY.INDEX_NAME,CONNECYBY.COLUMN_NAME
from ( select TABLE_OWNER SCHEMA_NAME,TABLE_NAME,INDEX_NAME,COLUMN_POSITION,trim(',' from sys_connect_by_path(COLUMN_NAME,',')) COLUMN_NAME
from DBA_IND_COLUMNS
start with COLUMN_POSITION = 1
connect by TABLE_OWNER = prior TABLE_OWNER
and TABLE_NAME = prior TABLE_NAME
and INDEX_NAME = prior INDEX_NAME
and COLUMN_POSITION = prior COLUMN_POSITION + 1) CONNECYBY
join ( select TABLE_OWNER SCHEMA_NAME,TABLE_NAME,INDEX_NAME,max(COLUMN_POSITION) COLUMN_POSITION
from DBA_IND_COLUMNS
group by TABLE_OWNER,TABLE_NAME,INDEX_NAME) MAX_CONNECYBY
on ( CONNECYBY.SCHEMA_NAME = MAX_CONNECYBY.SCHEMA_NAME
and CONNECYBY.TABLE_NAME = MAX_CONNECYBY.TABLE_NAME
and CONNECYBY.INDEX_NAME = MAX_CONNECYBY.INDEX_NAME
and CONNECYBY.COLUMN_POSITION = MAX_CONNECYBY.COLUMN_POSITION)
order by CONNECYBY.SCHEMA_NAME,CONNECYBY.TABLE_NAME,CONNECYBY.INDEX_NAME
dans SQL Server avec
CONNECTBY(SCHEMA_NAME,TABLE_NAME,INDEX_NAME,INDEX_COLUMN_ID,COLUMN_NAME)
as
( select SCHEMAS.NAME SCHEMA_NAME
, TABLES.NAME TABLE_NAME
, INDEXES.NAME INDEX_NAME
, INDEX_COLUMNS.INDEX_COLUMN_ID INDEX_COLUMN_ID
, cast(COLUMNS.NAME AS VARCHAR(MAX)) COLUMN_NAME
from SYS.INDEXES
join SYS.TABLES on (INDEXES.OBJECT_ID = TABLES.OBJECT_ID)
join SYS.SCHEMAS on (TABLES.SCHEMA_ID = SCHEMAS.SCHEMA_ID)
join SYS.INDEX_COLUMNS on ( INDEXES.OBJECT_ID = INDEX_COLUMNS.OBJECT_ID
and INDEX_COLUMNS.INDEX_ID = INDEXES.INDEX_ID)
join SYS.COLUMNS on ( INDEXES.OBJECT_ID = COLUMNS.OBJECT_ID
and INDEX_COLUMNS.COLUMN_ID = COLUMNS.COLUMN_ID)
where INDEX_COLUMNS.INDEX_COLUMN_ID = 1
union all
select SCHEMAS.NAME SCHEMA_NAME
, TABLES.NAME TABLE_NAME
, INDEXES.NAME INDEX_NAME
, INDEX_COLUMNS.INDEX_COLUMN_ID INDEX_COLUMN_ID
, cast(PRIOR.COLUMN_NAME + ',' + COLUMNS.NAME AS VARCHAR(MAX)) COLUMN_NAME
from SYS.INDEXES
join SYS.TABLES on (INDEXES.OBJECT_ID = TABLES.OBJECT_ID)
join SYS.SCHEMAS on (TABLES.SCHEMA_ID = SCHEMAS.SCHEMA_ID)
join SYS.INDEX_COLUMNS on ( INDEXES.OBJECT_ID = INDEX_COLUMNS.OBJECT_ID
and INDEX_COLUMNS.INDEX_ID = INDEXES.INDEX_ID)
join SYS.COLUMNS on ( INDEXES.OBJECT_ID = COLUMNS.OBJECT_ID
and INDEX_COLUMNS.COLUMN_ID = COLUMNS.COLUMN_ID)
join CONNECTBY as PRIOR on (SCHEMAS.NAME = PRIOR.SCHEMA_NAME
and TABLES.NAME = PRIOR.TABLE_NAME
and INDEXES.NAME = PRIOR.INDEX_NAME
and INDEX_COLUMNS.INDEX_COLUMN_ID = PRIOR.INDEX_COLUMN_ID + 1))
select CONNECTBY.SCHEMA_NAME,CONNECTBY.TABLE_NAME,CONNECTBY.INDEX_NAME,CONNECTBY.COLUMN_NAME
from CONNECTBY
join ( select SCHEMA_NAME
, TABLE_NAME
, INDEX_NAME
, MAX(INDEX_COLUMN_ID) INDEX_COLUMN_ID
from CONNECTBY
group by SCHEMA_NAME,TABLE_NAME,INDEX_NAME) MAX_CONNECTBY
on (CONNECTBY.SCHEMA_NAME = MAX_CONNECTBY.SCHEMA_NAME
and CONNECTBY.TABLE_NAME = MAX_CONNECTBY.TABLE_NAME
and CONNECTBY.INDEX_NAME = MAX_CONNECTBY.INDEX_NAME
and CONNECTBY.INDEX_COLUMN_ID = MAX_CONNECTBY.INDEX_COLUMN_ID)
order by CONNECTBY.SCHEMA_NAME,CONNECTBY.TABLE_NAME,CONNECTBY.INDEX_NAME
notez juste que si vous allez utiliser l'une des requêtes de travail ci-dessus pour script vos index, vous devez incorporer la colonne filter_definition de sys.table des index dans vos requêtes pour obtenir la définition de filtre des index non-groupés en SQL 2008+
AM
tout d'abord, veuillez noter que toutes les requêtes ci-dessus peuvent omettre ou incorporer par erreur les colonnes INCLUDE des indices. Il manque aussi dans certains cas l'option d'ordre et/ou ASC/DESC des colonnes.
a modifié la requête ci-dessus par jona. Comme mise à part, dans beaucoup de la base de données que j'utilise, j'installe ma propre fonction agrégée concaténée CLR, de sorte que le code ci-dessous dépend de quelque chose comme ceci étant présent. Les instructions SQL ci-dessus réduisent à un bien plus maintenable:
SELECT
s.[name] AS [schema_name]
, t.[name] AS [table_name]
, i.[name] AS [index_name]
, dbo.Concatenate(CASE WHEN ic.[key_ordinal] > 0 AND ic.[is_descending_key] = 1 THEN c.[name] + ' DESC' WHEN key_ordinal > 0 THEN c.[name] ELSE NULL END,',',1) AS [columns]
, dbo.Concatenate(CASE WHEN ic.[is_included_column] = 1 THEN c.[name] ELSE NULL END,',',1) AS [includes]
FROM
sys.tables t
INNER JOIN
sys.schemas s ON t.[schema_id] = s.[schema_id]
INNER JOIN
sys.indexes i ON i.[object_id] = t.[object_id]
INNER JOIN
sys.index_columns ic ON ic.[object_id] = t.[object_id] AND ic.index_id = i.index_id
INNER JOIN
sys.columns c ON c.[object_id] = t.[object_id] AND ic.column_id = c.column_id
GROUP BY
s.[name]
, t.[name]
, i.[name]
ORDER BY
s.[name]
, t.[name]
, i.[name]
il y a beaucoup de agrégats de concaténation dehors si votre environnement permet des fonctions basées CLR ajoutées à elle.
pour les colonnes uniques par index:
select s.name, t.name, i.name, i.index_id,c.name,c.column_id
from sys.schemas s
inner join sys.tables t on t.schema_id = s.schema_id
inner join sys.indexes i on i.object_id = t.object_id
inner join sys.index_columns ic on ic.object_id = t.object_id
and ic.index_id=i.index_id
inner join sys.columns c on c.object_id = t.object_id
and ic.column_id = c.column_id
where i.object_id = object_id('previous.account_1')
order by index_id,column_id
la requête ci-dessous inclut toutes les informations pertinentes pour les index définis par l'Utilisateur, (pas d'index pour les contraintes uniques et les clés primaires) avec toutes les colonnes:
SELECT I.name as IndexName,
CASE WHEN I.is_unique = 1 THEN 'Yes' ELSE 'No' END as 'Unique',
I.type_desc COLLATE DATABASE_DEFAULT as Index_Type,
'[' + SCHEMA_NAME(T.schema_id) + ']' as 'Schema',
'[' + T.name + ']' as TableName,
STUFF((SELECT ', [' + C.name + CASE WHEN IC.is_descending_key = 0 THEN '] ASC' ELSE '] DESC' END
FROM sys.index_columns IC INNER JOIN sys.columns C ON IC.object_id = C.object_id AND IC.column_id = C.column_id
WHERE IC.is_included_column = 0 AND IC.object_id = I.object_id AND IC.index_id = I.Index_id
FOR XML PATH('')), 1, 2, '') as Key_Columns,
Included_Columns,
I.filter_definition,
CASE WHEN I.is_padded = 1 THEN 'ON' ELSE 'OFF' END as PAD_INDEX,
CASE WHEN ST.no_recompute = 0 THEN 'OFF' ELSE 'ON' END as [Statistics_Norecompute],
CONVERT(VARCHAR(5), CASE WHEN I.fill_factor = 0 THEN 100 ELSE I.fill_factor END) as [Fillfactor],
CASE WHEN I.ignore_dup_key = 1 THEN 'ON' ELSE 'OFF' END as [Ignore_Dup_Key],
CASE WHEN I.allow_row_locks = 1 THEN 'ON' ELSE 'OFF' END as [Allow_Row_Locks],
CASE WHEN I.allow_page_locks = 1 THEN 'ON' ELSE 'OFF' END [Allow_Page_Locks]
FROM sys.indexes I INNER JOIN
sys.tables T ON T.object_id = I.object_id INNER JOIN
sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id INNER JOIN
sys.data_spaces DS ON I.data_space_id = DS.data_space_id INNER JOIN
sys.filegroups FG ON I.data_space_id = FG.data_space_id LEFT OUTER JOIN
(SELECT * FROM
(SELECT IC2.object_id, IC2.index_id,
STUFF((SELECT ', ' + C.name FROM sys.index_columns IC1 INNER 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, '') as Included_Columns
FROM sys.index_columns IC2
GROUP BY IC2.object_id, IC2.index_id) tmp1
WHERE Included_Columns 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;
en bonus, la requête ci-dessous est formatée pour écrire les scripts create index et drop index:
SELECT I.name as IndexName,
-- Uncommnent line below to include checking for index exists as part of the script
--'IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = '''+ I.name +''') ' +
'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 + '] (' + STUFF(
(SELECT ', [' + C.name + CASE WHEN IC.is_descending_key = 0 THEN '] ASC' ELSE '] DESC' END
FROM sys.index_columns IC INNER JOIN sys.columns C ON IC.object_id = C.object_id AND IC.column_id = C.column_id
WHERE IC.is_included_column = 0 AND IC.object_id = I.object_id AND IC.index_id = I.Index_id
FOR XML PATH('')), 1, 2, '') + ') ' +
ISNULL(' INCLUDE (' + IncludedColumns + ') ', '') +
ISNULL(' WHERE ' + I.filter_definition, '') +
'WITH (PAD_INDEX = ' + CASE WHEN I.is_padded = 1 THEN 'ON' ELSE 'OFF' END +
', STATISTICS_NORECOMPUTE = ' + CASE WHEN ST.no_recompute = 0 THEN 'OFF' ELSE 'ON' END +
', SORT_IN_TEMPDB = OFF' +
', FILLFACTOR = ' + CONVERT(VARCHAR(5), CASE WHEN I.fill_factor = 0 THEN 100 ELSE I.fill_factor END) +
', IGNORE_DUP_KEY = ' + CASE WHEN I.ignore_dup_key = 1 THEN 'ON' ELSE 'OFF' END +
', ONLINE = OFF' +
', ALLOW_ROW_LOCKS = ' + CASE WHEN I.allow_row_locks = 1 THEN 'ON' ELSE 'OFF' END +
', ALLOW_PAGE_LOCKS = ' + CASE WHEN I.allow_page_locks = 1 THEN 'ON' ELSE 'OFF' END +
') ON [' + DS.name + '];' + CHAR(13) + CHAR(10) + 'GO' as [CreateIndex],
'DROP INDEX ['+ I.name +'] ON ['+ SCHEMA_NAME(T.schema_id) +'].['+ T.name +'];' +
CHAR(13) + CHAR(10) + 'GO' AS [DropIndex]
FROM sys.indexes I INNER JOIN
sys.tables T ON T.object_id = I.object_id INNER JOIN
sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id INNER JOIN
sys.data_spaces DS ON I.data_space_id = DS.data_space_id INNER JOIN
sys.filegroups FG ON I.data_space_id = FG.data_space_id LEFT OUTER JOIN
(SELECT * FROM
(SELECT IC2.object_id, IC2.index_id,
STUFF((SELECT ', ' + C.name FROM sys.index_columns IC1 INNER 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, '') as IncludedColumns
FROM sys.index_columns IC2
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
Voici la meilleure façon de le faire:
SELECT sys.tables.object_id, sys.tables.name as table_name, sys.columns.name as column_name, sys.indexes.name as index_name,
sys.indexes.is_unique, sys.indexes.is_primary_key
FROM sys.tables, sys.indexes, sys.index_columns, sys.columns
WHERE (sys.tables.object_id = sys.indexes.object_id AND sys.tables.object_id = sys.index_columns.object_id AND sys.tables.object_id = sys.columns.object_id
AND sys.indexes.index_id = sys.index_columns.index_id AND sys.index_columns.column_id = sys.columns.column_id)
AND sys.tables.name = 'your_table_name'
je préfère utiliser des jointures implicites car il est beaucoup plus facile pour moi de comprendre. Vous pouvez supprimer la référence object_id car vous n'en avez peut-être pas besoin.
santé.
en utilisant SQL Server 2016, cela donne une liste complète de tous les index, avec un dump inclus de chaque table de sorte que vous pouvez voir comment les tables se rapportent. Il montre également les colonnes incluses dans les indices de couverture:
select t.name TableName, i.name IdxName, c.name ColName
, ic.index_column_id ColPosition
, i.type_desc Type
, case when i.is_primary_key = 1 then 'Yes' else '' end [Primary?]
, case when i.is_unique = 1 then 'Yes' else '' end [Unique?]
, case when ic.is_included_column = 0 then '' else 'Yes - Included' end [CoveredColumn?]
, 'indexes >>>>' [*indexes*], i.*, 'index_columns >>>>' [*index_columns*]
, ic.*, 'tables >>>>' [*tables*]
, t.*, 'columns >>>>' [*columns*], c.*
from sys.index_columns ic
join sys.tables t on t.object_id = ic.object_id
join sys.columns c on c.object_id = t.object_id and c.column_id = ic.column_id
join sys.indexes i on i.object_id = t.object_id and i.index_id = ic.index_id
order by TableName, IdxName, ColPosition
j'ai utilisé la requête suivante lorsque j'ai eu cette exigence...
SELECT
TableName = t.name,
ColumnId = col.column_id,
ColumnName = col.name,
DataType = ty.name,
MaxSize = ty.max_length,
IsNullable = CASE WHEN (col.is_nullable = 1) THEN 'Y' END,
IsIdentity = CASE WHEN (col.is_identity = 1) THEN 'Y' END,
IsPrimaryKey = CASE WHEN (ic.column_id = col.column_id) THEN 'Y' END,
IsForeignKey = CASE WHEN (fkc.parent_column_id = col.column_id) THEN 'Y' END,
IsDefault = CASE WHEN (dc.parent_column_id = col.column_id) THEN 'Y' END
FROM
sys.tables t
INNER JOIN
sys.columns col ON t.object_id = col.object_id
LEFT JOIN
sys.indexes ind ON t.object_id = ind.object_id
LEFT JOIN
sys.index_columns ic ON ic.index_id=ind.index_id AND ic.object_id = col.object_id and ic.column_id = col.column_id
LEFT JOIN sys.foreign_key_columns fkc
ON fkc.parent_object_id = col.object_id AND fkc.parent_column_id=col.column_id
LEFT JOIN sys.default_constraints dc
ON dc.parent_object_id = col.object_id AND dc.parent_column_id=col.column_id
LEFT JOIN
sys.types ty on ty.user_type_id = col.user_type_id
WHERE
--t.name='<TABLENAME>'
t.schema_id = 10 --SCHEMA ID
AND ind.is_primary_key=1
ORDER BY
t.name, ColumnId
sELECT
TableName = t.name,
IndexName = ind.name,
--IndexId = ind.index_id,
ColumnId = ic.index_column_id,
ColumnName = col.name,
key_ordinal,
ind.type_desc
--ind.*,
--ic.*,
--col.*
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 t.name='CompanyReconciliation' --table name
and key_ordinal>0
ORDER BY
t.name, ind.name, ind.index_id, ic.index_column_id