반응형
SQL Server DB의 모든 인덱스 및 인덱스 열 목록
SQL Server 2005+의 모든 인덱스 및 인덱스 컬럼 목록을 가져오려면 어떻게 해야 합니까?내가 얻을 수 있는 가장 가까운 것은:
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
그건 정확히 내가 원하는 게 아니야.
필요한 것은 모든 사용자 정의 인덱스(즉, 고유한 제약조건과 기본 키를 지원하는 인덱스가 없음)를 모든 열(인덱스 정의에 표시되는 방법에 따라 정렬됨)과 가능한 한 많은 메타데이터를 나열하는 것입니다.
수 「sys 「sys」의 2종류가 .sys.indexes ★★★★★★★★★★★★★★★★★」sys.index_columns.
인덱스와 해당 열에 대해 필요한 모든 정보를 제공합니다.
편집: 이 쿼리는 원하는 것에 매우 근접했습니다.
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.is_included_column, ic.key_ordinal;
.sp_helpindex한 테이블의 모든 인덱스를 표시합니다.
EXEC sys.sp_helpindex @objname = N'User' -- nvarchar(77)
에 대해서, 「」를 할 수 있습니다.sys.objects각 테이블의 모든 인덱스를 가져옵니다.
위의 어느 것도 나에게 도움이 되지 않았지만, 이것은 다음과 같다.
-- 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)
이름에 공백이 포함되어 있는 경우 작성 스크립트에서 공백 주위에 대괄호를 추가합니다.
마지막 키 열이 모두 null이면 누락된 키가 없음을 알 수 있습니다.
원래의 요구와 같이 프라이머리 키등을 제외하는 것은 간단하다.
메모: 이 솔루션은 색인화된 열과 포함된 열을 구분하지 않으므로 주의하십시오.
--짧고 달콤함:
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]
다음은 SQL Server 2014/2016 및 Microsoft Azure SQL Database에서 작동합니다.
슬라이스 및 다이싱을 위해 Notepad/Excel로 쉽게 내보낼 수 있는 포괄적인 결과 세트 생성 및 다음을 포함합니다.
- 테이블명
- 인덱스명
- 인덱스 설명
- 색인화된 열 - 순서대로
- 포함된 열 - 순서대로
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;
특정 인덱스, 인덱스 열 및 포함된 열도 가져와야 했습니다.사용한 쿼리는 다음과 같습니다.
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])
저는 통과하지 못했지만, 원작자가 올린 질문에서 원하는 것을 얻었습니다.
필요에 따라 (조건/필터 없이) 사용했는데 잘못된 결과가 나왔습니다.
주요 문제는 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
다음은 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
수락된 답변과 다른 두 가지 질문을 바탕으로 다음 질문을 정리했습니다.
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
이 쿼리는 인덱스, 해당 열 및 사용 현황을 보여주는 다음과 같은 결과를 반환합니다.어떤 지수가 다른 지수보다 더 나은지 결정하는 데 매우 유용합니다.
이것은 동작합니다.
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
이렇게 해도 테이블 이름이 다시 나타나지 않고 인덱스가 없는 모든 테이블에 대해 경고가 표시됩니다. 문제가 있는 경우 인덱스가 있는 테이블 위에 다음과 같은 루프를 만들 수 있습니다.
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
★★★★
필요에 따라서, 데이터를 필터링 할 수 있습니다.다음의 예를 나타냅니다(이러한 방법은 어느쪽이든 유효합니다).
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
이것은 인덱스로 되돌리는 방법입니다.SHOW CONTIG 를 사용해 플래그멘테이션을 평가할 수 있습니다.데이터베이스 또는 테이블에 대한 모든 인덱스와 통계 정보가 나열됩니다.대용량 데이터베이스에서는 장기간 실행될 수 있습니다.이 접근방식의 장점 중 하나는 관리자가 사용할 필요가 없다는 것입니다.
--데이터베이스의 모든 인덱스에 대한 단편화 정보 표시
SET NOCOUNT ON
USE pubs
DBCC SHOWCONTIG WITH ALL_INDEXES
GO
...완료하면 NOCOUNT를 해제
--테이블의 모든 인덱스에 대한 단편화 정보 표시
SET NOCOUNT ON
USE pubs
DBCC SHOWCONTIG (authors) WITH ALL_INDEXES
GO
--특정 인덱스에 대한 단편화 정보 표시
SET NOCOUNT ON
USE pubs
DBCC SHOWCONTIG (authors,aunmind)
GO
이것은 하나의 디폴트 스키마로 동작하지만 쉽게 개선할 수 있습니다.SQLQueries를 사용하여 3개의 컬럼ns - Create / Drop / Rebuild (재구성 없음)
쿼리:
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
산출량
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
내가 이걸 생각해냈는데, 이게 내가 필요로 하는 정확한 개요를 제공하는 거야.인덱스 열이 집계되는 인덱스당 하나의 행을 얻을 수 있습니다.
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
이 포화상태의 질문에 또 다른 대답을 해도 될까요?
이것은 @marc_s 답변의 자유로운 재작성이며, @Tim Ford의 몇 가지 내용과 혼합된 것으로, 좀 더 깔끔하고 심플한 결과 세트와 현재의 요구에 맞는 최종 디스플레이와 오더를 목표로 하고 있습니다.
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
이것을 사용해 보세요.
EXEC sys.sp_helpindex @objname = 'mytable';
Tim Ford 코드에 근거하면, 다음과 같은 답이 나옵니다.
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]
다음 쿼리에는 사용자 정의 인덱스에 대한 모든 관련 정보(고유 제약 조건 및 기본 키에 대한 인덱스는 없음)가 모든 열에 포함됩니다.
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;
추가 보너스로 다음 쿼리는 인덱스 작성 및 인덱스 드롭 스크립트를 작성하도록 포맷되어 있습니다.
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
프로파일에 사용하고 있는 것이 기재되어 있기 때문입니다.NET SMO(Server Managed Objects)를 프로그래밍 방식으로 사용할 수 있습니다.그렇지 않으면 위의 답변 중 하나가 환상적입니다.
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
SQL Server의 경우
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
첫째, 위의 모든 쿼리는 인덱스의 INCLUDE 열을 누락하거나 잘못 포함할 수 있습니다.또한 일부에서는 열의 올바른 순서 지정 및/또는 ASC/DESC 옵션이 누락되어 있습니다.
위의 쿼리를 jona에 의해 수정했습니다.한편, 내가 사용하는 대부분의 데이터베이스에서는, 독자적인 CLR CONNECATENATE 집약 함수를 인스톨 하고 있기 때문에, 이하의 코드는, 이러한 기능이 존재하는 것에 의해서 다릅니다.위의 SQL 문을 사용하면 유지보수가 훨씬 쉬워집니다.
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]
환경에서 CLR 기반 함수를 추가할 수 있는 경우 많은 연결 집약이 있습니다.
인덱스당 고유 열의 경우:
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
SQL Server 2016을 사용하면 테이블이 어떻게 관련되어 있는지 볼 수 있도록 각 테이블의 덤프가 포함된 모든 인덱스의 전체 목록이 표시됩니다.또한 커버링 인덱스에 포함된 열도 보여 줍니다.
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
가장 좋은 방법은 다음과 같습니다.
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'저는 암묵적인 조인을 사용하는 것이 훨씬 이해하기 쉽기 때문에 선호합니다.object_id 참조는 필요하지 않을 수 있으므로 삭제할 수 있습니다.
건배.
이 요구가 있었을 때, 이하의 문의를 사용해 왔습니다.
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
SQL Server 2014에서 작동하는 솔루션.여기 출력 필드 몇 개만 포함했는데 원하는 만큼 추가해도 됩니다.
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
KFD9의 답변을 업데이트했습니다.
include-specification을 지원하고 권장되지 않는 indexkey_property를 사용하지 않도록 버전을 수정했습니다.
그러면 인덱스 및 제약 조건에 대한 생성 및 삭제 문이 제공됩니다.
with indexes as (
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)+' ' as 'Type', -- B=basic, C=Clustered, X=XML
(select string_agg(CAST('[' + c.name + ']' + case when is_descending_key = 1 then ' DESC' else '' end AS NVARCHAR(MAX)), ',') within group (order by index_column_id)
from sys.index_columns ic JOIN sys.columns c on ic.column_id = c.column_id and ic.object_id = c.object_id where ic.index_id = si.index_id and ic.object_id = si.object_id and ic.is_included_column = 0) Cols,
(select string_agg(CAST('[' + c.name + ']' + case when is_descending_key = 1 then ' DESC' else '' end AS NVARCHAR(MAX)), ',') within group (order by index_column_id)
from sys.index_columns ic JOIN sys.columns c on ic.column_id = c.column_id and ic.object_id = c.object_id where ic.index_id = si.index_id and ic.object_id = si.object_id and ic.is_included_column = 1) IncludedCols,
(select count(*) from sys.index_columns ic where ic.index_id = si.index_id and ic.object_id = si.object_id) IndexColsCount
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
)
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)+
' ('+Cols+')'+
isnull(' include ('+IncludedCols+')', '')+
'' as CreateIndex,
CASE pk
WHEN 'PK' THEN 'ALTER '+
'TABLE ['+SchemaName+'].['+TableName+'] DROP CONSTRAINT ['+IndexName+'] '
ELSE 'DROP INDEX ['+IndexName+'] ON ['+SchemaName+'].['+TableName + ']'
END AS DropIndex,
IndexColsCount
FROM indexes
ORDER BY SchemaName,TableName,IndexName
올바른 것은 여기에 있습니다(위의 모든 게시물은 테이블에 여러 개의 인덱스가 있는 경우 데카르트 제품 결과를 제공합니다).
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
AND i.index_id = ic.index_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
AND t.name = 'DimCustomer'
order by ic.key_ordinal
select i.object_id, i.name as [index] , STRING_AGG(c.name,', ') as [column], o.name as [table] from sys.indexes i
INNER join sys.index_columns ic on ic.object_id = i.object_id and ic.index_id = i.index_id
INNER join sys.columns c on c.object_id = ic.object_id and ic.column_id = c.column_id
INNER JOIN sys.objects o on o.object_id = i.object_id
where i.object_id > 100 and i.is_primary_key = 0 and i.is_unique = 0 and o.is_ms_shipped <> 1
group by i.object_id, i.name, o.name
order by i.name
SQL 2016 이상에서는 고유하지 않고 기본 키가 없는 경우 object_id, 인덱스 이름, 열 및 tablename을 표시합니다.
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
언급URL : https://stackoverflow.com/questions/765867/list-of-all-index-index-columns-in-sql-server-db
반응형
'programing' 카테고리의 다른 글
| SQL Server Management Studio에서 컴포지트 키를 만드는 방법 (0) | 2023.04.06 |
|---|---|
| 날짜/시간을 삽입하는 동안 문자열에서 날짜 및/또는 시간을 변환할 때 변환에 실패했습니다. (0) | 2023.04.06 |
| 2개의 SQL Server 데이터베이스(스케마 및 데이터)를 비교하기 위한 최적의 툴은 무엇입니까? (0) | 2023.04.06 |
| 테이블 추가 열 구문 변경 (0) | 2023.04.06 |
| 하위 쿼리가 EXISTS에 도입되지 않은 경우 선택 목록에서 식을 하나만 지정할 수 있습니다. (0) | 2023.04.06 |