Liste aller Index- und Indexspalten in der SQL Server-Datenbank
Wie erhalte ich eine Liste aller Index- und Indexspalten in SQL Server 2005+? Ich könnte am nächsten kommen:
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
Welches ist nicht genau das, was ich will.
Ich möchte alle benutzerdefinierten Indizes auflisten ( was bedeutet, dass keine Indizes, die eindeutige Integritätsbedingungen und Primärschlüssel unterstützen ) mit allen Spalten (geordnet nach der Indexdefinition) plus so viel Metadaten wie möglich.
Es gibt zwei "sys" -Katalogsichten, die Sie einsehen können:
select * from sys.indexes
select * from sys.index_columns
Diese geben Ihnen so gut wie alle Informationen, die Sie möglicherweise über Indizes und deren Spalten wünschen.
BEARBEITEN: Diese Abfrage kommt dem, was Sie suchen, ziemlich nahe:
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;
Mit dem sp_helpindex können Sie alle Indizes einer Tabelle anzeigen.
EXEC sys.sp_helpindex @objname = N'User' -- nvarchar(77)
Für alle Indizes können Sie sys.objects durchlaufen, um alle Indizes für jede Tabelle abzurufen.
Keines der oben genannten Dinge hat die Arbeit für mich erledigt, aber dies tut:
-- 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)
Wenn Ihre Namen Leerzeichen enthalten, fügen Sie sie in den Erstellungsskripts mit eckigen Klammern ein.
Wenn die letzte Schlüsselspalte alle Nullen ist, wissen Sie, dass keine fehlen.
Das Herausfiltern von Primärschlüsseln usw. wie in der ursprünglichen Anforderung ist trivial.
HINWEIS: Seien Sie vorsichtig mit dieser Lösung, da indizierte und eingeschlossene Spalten nicht unterschieden werden.
--Kurz und bündig:
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]
Folgendes gibt an, was ähnlich ist wie sp_helpindex Tabellenname
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
Ich musste auch bestimmte Indizes, ihre Indexspalten und ihre eingeschlossenen Spalten erhalten. Hier ist die Abfrage, die ich verwendet habe:
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])
Hey Leute, ich habe es nicht durchgemacht, aber ich habe das bekommen, was ich in der vom ursprünglichen Autor gestellten Abfrage wollte.
Ich habe es (ohne Bedingungen/Filter) für meine Anforderung verwendet, aber es gab falsche Ergebnisse
Das Hauptproblem war die Ergebnisse, die Cross-Produkte ohne Join-Bedingung für index_id erhalten
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
Das Folgende funktioniert auf SQL Server 2014/2016 sowie auf jeder Microsoft Azure SQL-Datenbank.
Erzeugt ein umfassendes Resultset, das leicht in Notepad/Excel exportiert werden kann, um es zu schneiden und zu schneiden
- Tabellenname
- Indexname
- Index Beschreibung
- Indizierte Spalten - In Reihenfolge
- Enthaltene Spalten - In Reihenfolge
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;
das wird funktionieren:
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
dadurch wird der Tabellenname nicht wiederhergestellt, und Sie erhalten Warnungen für alle Tabellen ohne Index. Wenn dies ein Problem ist, können Sie eine Schleife über die Tabellen erstellen, die folgende Indizes haben:
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 @[email protected]@ROWCOUNT,@CurrentRow=1
WHILE @CurrentRow<[email protected]
BEGIN
SELECT @CurrentTable=TableName FROM @Tables WHERE [email protected]
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 @[email protected]+1
END --WHILE
SELECT * from @IndexInfo
EDIT
Wenn Sie möchten, können Sie die Daten filtern. Hier einige Beispiele (diese funktionieren für beide Methoden):
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
Basierend auf der akzeptierten Antwort und zwei weiteren Fragen 1 , 2 Ich habe folgende Abfrage zusammengestellt:
SELECT
QUOTENAME(t.name) AS TableName,
QUOTENAME(i.name) AS IndexName,
i.is_primary_key,
i.is_unique,
i.is_unique_constraint,
STUFF(REPLACE(REPLACE((
SELECT QUOTENAME(c.name) + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END AS [data()]
FROM sys.index_columns AS ic
INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.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
), '<row>', ', '), '</row>', ''), 1, 2, '') AS KeyColumns,
STUFF(REPLACE(REPLACE((
SELECT QUOTENAME(c.name) AS [data()]
FROM sys.index_columns AS ic
INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.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.index_column_id
FOR XML PATH
), '<row>', ', '), '</row>', ''), 1, 2, '') AS IncludedColumns,
u.user_seeks,
u.user_scans,
u.user_lookups,
u.user_updates
FROM sys.tables AS t
INNER JOIN sys.indexes AS i ON t.object_id = i.object_id
LEFT JOIN sys.dm_db_index_usage_stats AS u ON i.object_id = u.object_id AND i.index_id = u.index_id
WHERE t.is_ms_shipped = 0
AND i.type <> 0
Diese Abfrage gibt Ergebnisse wie unten zurück, die die Liste der Indizes, ihre Spalten und ihre Verwendung anzeigen. Sehr hilfreich bei der Bestimmung, welcher Index besser abschneidet als andere:
Dies ist eine Möglichkeit, in die Indizes zu gelangen. Sie können SHOWCONTIG verwenden, um die Fragmentierung zu bewerten. Es listet alle Indizes für die Datenbank oder Tabelle sowie Statistiken auf. Ich würde darauf hinweisen, dass es auf einer großen Datenbank lange dauern kann. Für mich ist einer der Vorteile dieses Ansatzes, dass Sie kein Administrator sein müssen, um ihn zu verwenden.
- Fragmentierungsinformationen für alle Indizes in einer Datenbank anzeigen
SET NOCOUNT ON
USE pubs
DBCC SHOWCONTIG WITH ALL_INDEXES
GO
... schalten Sie NOCOUNT ab, wenn Sie fertig sind
- Fragmentierungsinfo für alle Indizes einer Tabelle anzeigen
SET NOCOUNT ON
USE pubs
DBCC SHOWCONTIG (authors) WITH ALL_INDEXES
GO
- Fragmentierungsinformationen für einen bestimmten Index anzeigen
SET NOCOUNT ON
USE pubs
DBCC SHOWCONTIG (authors,aunmind)
GO
Darf ich eine andere Antwort auf diese gesättigte Frage wagen?
Dies ist eine liberale Überarbeitung der Antwort von @marc_s, gemischt mit einigen Dingen von @ Tim Ford, mit dem Ziel, ein bisschen sauberere und einfachere Ergebnismenge und eine abschließende Anzeige und Reihenfolge für meine aktuellen Bedürfnisse zu haben.
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
Ich habe mir dieses ausgedacht, das mir den genauen Überblick gibt, den ich brauche. Was hilft, ist, dass Sie pro Index eine Zeile erhalten, in der die Indexspalten zusammengefasst werden.
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
basierend auf Tim Ford-Code ist dies die richtige Antwort:
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]
Beachten Sie einfach, dass Sie, wenn Sie eine der obigen Arbeitsabfragen zum Skriptieren Ihrer Indizes verwenden möchten, die Spalte filter_definition aus der Tabelle sys.indexes in Ihre Abfragen integrieren müssen, um die Filterdefinition nicht gruppierter Indizes in SQL 2008+ zu erhalten
AM
Die obige Lösung ist elegant, aber laut MS wird INDEXKEY_PROPERTY nicht mehr empfohlen. Siehe: http://msdn.Microsoft.com/de-de/library/ms186773.aspx
In 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
In SQL Server mit .__
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
Da in Ihrem Profil angegeben wird, dass Sie .NET verwenden, können Sie könnte Server Managed Objects (SMO) programmgesteuert verwenden. Andernfalls sind alle obigen Antworten fantastisch.
Die folgende Abfrage enthält alle relevanten Informationen für die benutzerdefinierten Indizes (keine Indizes für eindeutige Integritätsbedingungen und Primärschlüssel) mit allen Spalten:
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;
Als zusätzlicher Bonus wird die folgende Abfrage so formatiert, dass sie die Indexe zum Erstellen von Indizes und zum Löschen der Indexe ausschreibt:
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
Für eindeutige Spalten pro 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
Beachten Sie zunächst, dass bei allen obigen Abfragen die INCLUDE -Spalten der Indizes möglicherweise fehlen oder falsch eingefügt werden. In einigen fehlt auch die richtige Reihenfolge und/oder ASC/DESC-Option der Spalten.
Die obige Abfrage wurde von jona geändert. Abgesehen davon installiere ich in vielen Datenbanken, die ich verwende, meine eigene Aggregatfunktion CLR CONCATENATE, sodass der folgende Code davon abhängt, dass so etwas vorhanden ist. Die obigen SQL-Anweisungen reduzieren sich auf ein viel mehr zu pflegendes:
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]
Es gibt viele Verkettungsaggregate , wenn Ihre Umgebung CLR-basierte Funktionen zulässt.
Bei Verwendung von SQL Server 2016 wird eine vollständige Liste aller Indizes mit einem integrierten Speicherauszug für jede Tabelle angezeigt, damit Sie sehen können, wie sich die Tabellen beziehen. Es zeigt auch Spalten, die in überdeckenden Indizes enthalten sind:
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
Hier ist der beste Weg, dies zu tun:
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'Ich bevorzuge die Verwendung von impliziten Verknüpfungen, da dies für mich viel einfacher zu verstehen ist. Sie können die object_id-Referenz entfernen, da Sie sie möglicherweise nicht benötigen.
Prost.
Dies ist meins, funktioniert mit einem Standardschema, kann aber leicht verbessert werden. Es gibt 3 Spalten mit SQLQueries - Erstellen/Löschen/Wiederherstellen
Abfrage:
SELECT
'CREATE ' +
CASE WHEN is_primary_key=1 THEN 'CLUSTERED'
WHEN is_primary_key=0 and is_unique_constraint=0 THEN 'NONCLUSTERED'
WHEN is_primary_key=0 and is_unique_constraint=1 THEN 'UNIQUE' END
+ ' INDEX ' +
QUOTENAME(i.name) + ' ON ' +
QUOTENAME(t.name) + ' ( ' +
STUFF(REPLACE(REPLACE((
SELECT QUOTENAME(c.name) + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END AS [data()]
FROM sys.index_columns AS ic
INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.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
), '<row>', ', '), '</row>', ''), 1, 2, '') + ' ) ' -- keycols
+ COALESCE(' INCLUDE ( ' +
STUFF(REPLACE(REPLACE((
SELECT QUOTENAME(c.name) AS [data()]
FROM sys.index_columns AS ic
INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.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.index_column_id
FOR XML PATH
), '<row>', ', '), '</row>', ''), 1, 2, '') + ' ) ', -- included cols
'') as [Create],
'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(t.name) as [Drop],
'ALTER INDEX ' + QUOTENAME(i.name) + ' ON ' +QUOTENAME(t.name) + ' REBUILD ' as [Rebuild]
FROM sys.tables AS t
INNER JOIN sys.indexes AS i ON t.object_id = i.object_id
LEFT JOIN sys.dm_db_index_usage_stats AS u ON i.object_id = u.object_id AND i.index_id = u.index_id
WHERE t.is_ms_shipped = 0
AND i.type <> 0
order by QUOTENAME(t.name), is_primary_key desc
Ausgabe
Create Drop Rebuild
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE CLUSTERED INDEX [PK_Table1] ON [Table1] ( [Tab1_ID] ) DROP INDEX [PK_Table1] ON [Table1] ALTER INDEX [PK_Table1] ON [Table1] REBUILD
CREATE UNIQUE INDEX [IX_Table1_Name] ON [Table1] ( [Tab1_Name] ) DROP INDEX [IX_Table1_Name] ON [Table1] ALTER INDEX [IX_Table1_Name] ON [Table1] REBUILD
CREATE NONCLUSTERED INDEX [IX_Table2] ON [Table2] ( [Tab2_Name], [Tab2_City] ) INCLUDE ( [Tab2_PhoneNo] ) DROP INDEX [IX_Table2] ON [Table2] ALTER INDEX [IX_Table2] ON [Table2] REBUILD
Arbeitslösung für SQL Server 2014. Ich habe hier nur eine Handvoll Ausgabefelder eingefügt, kann aber beliebig viele hinzufügen.
SELECT
o.object_id AS objectId
,o.name AS objectName
,i.index_id AS indexId
,i.name AS indexName
,i.type_desc AS typeDesc
,ic.index_column_id AS indexColumnId
,ic.key_ordinal AS keyOrdinal
,ic.is_included_column AS isIncludedColumn
,ic.column_id AS columnId
,c.name AS columnName
FROM {database}.sys.objects AS o
INNER JOIN {database}.sys.columns AS c ON
c.object_id = o.object_id
AND o.type = 'U'
INNER JOIN {database}.sys.indexes AS i ON
i.object_id = o.object_id
INNER JOIN {database}.sys.index_columns AS ic ON
ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.column_id = c.column_id
ORDER BY
o.object_id
,i.index_id
,ic.index_column_id
Ich habe die folgende Abfrage verwendet, als ich diese Anforderung hatte ...
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