sql server - एसक्यूएल सर्वर डीबी में सभी इंडेक्स और इंडेक्स कॉलम की सूची




sql-server tsql (17)

--छोटा एवं सुन्दर:

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 सर्वर 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

जो बिल्कुल मैं नहीं चाहता हूं।
मैं चाहता हूं कि सभी उपयोगकर्ता परिभाषित इंडेक्स सूचीबद्ध करें, ( जिसका मतलब है कि कोई भी अनुक्रमणिका जो अनन्य बाधाओं और प्राथमिक कुंजी का समर्थन करती है ) सभी स्तंभों के साथ (इंडेक्स परिभाषा में वे कैसे दिखाई देते हैं) के साथ-साथ जितना संभव हो उतना मेटाडेटा।


SQL Server 2014/2016 के साथ-साथ किसी भी Microsoft Azure SQL डेटाबेस पर निम्न कार्य करता है।

एक व्यापक परिणाम सेट तैयार करता है जो आसानी से नोटपैड / एक्सेल को स्लाइसिंग और डाइसिंग के लिए निर्यात योग्य है और इसमें शामिल है

  1. तालिका नाम
  2. सूचकांक का नाम
  3. सूचकांक विवरण
  4. अनुक्रमित कॉलम - क्रम में
  5. शामिल कॉलम - क्रम में
 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;

आप एक तालिका के सभी इंडेक्स देखने के लिए sp_helpindex का उपयोग कर सकते हैं।

EXEC sys.sp_helpindex @objname = N'User' -- nvarchar(77)

और सभी इंडेक्स के लिए, आप प्रत्येक तालिका के लिए सभी इंडेक्स प्राप्त करने के लिए sys.objects को पार कर सकते हैं।


इसे करने का सबसे अच्छा तरीका यहां दिया गया है:

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 संदर्भ को हटा सकते हैं क्योंकि आपको इसकी आवश्यकता नहीं हो सकती है।

चीयर्स।


उपर्युक्त समाधान सुरुचिपूर्ण है, लेकिन एमएस के अनुसार, INDEXKEY_PROPERTY को बहिष्कृत किया जा रहा है। देखें: msdn.microsoft.com/en-us/library/ms186773.aspx


ओरेकल में

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

एसक्यूएल सर्वर के साथ

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

चूंकि आपकी प्रोफ़ाइल बताती है कि आप .NET का उपयोग कर रहे हैं, आप सर्वर प्रबंधित ऑब्जेक्ट्स (एसएमओ) प्रोग्रामेटिक रूप से उपयोग कर सकते हैं ... अन्यथा उपरोक्त में से कोई भी उत्तर शानदार है।


टिम फोर्ड कोड के आधार पर, यह सही जवाब है:

  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]

निम्नलिखित देता है जो 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 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;

As an added bonus, the below query is formatted to write out the create index and drop index scripts:

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 

बस ध्यान दें कि यदि आप अपने इंडेक्स को स्क्रिप्ट करने के लिए उपर्युक्त कामकाजी प्रश्नों में से किसी एक का उपयोग करने जा रहे हैं, तो आपको SQL क्वेरी+ में गैर क्लस्टर इंडेक्स की फ़िल्टर परिभाषा प्राप्त करने के लिए अपने प्रश्नों में sys.indexes तालिका से filter_definition कॉलम को शामिल करने की आवश्यकता है।

AM


मुझे विशेष इंडेक्स, उनके इंडेक्स कॉलम और उनके शामिल कॉलम भी प्राप्त करने की आवश्यकता है। यहां दी गई क्वेरी है:

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])

यह इंडेक्स में बैक करने का एक तरीका है। आप विखंडन का आकलन करने के लिए SHOWCONTIG का उपयोग कर सकते हैं। यह आंकड़ों के साथ डेटाबेस या तालिका के लिए सभी इंडेक्स सूचीबद्ध करेगा। मैं सावधानी बरतता हूं कि एक बड़े डेटाबेस पर, यह लंबे समय तक चल सकता है। मेरे लिए, इस दृष्टिकोण के लाभों में से एक यह है कि आपको इसका उपयोग करने के लिए एक व्यवस्थापक होने की आवश्यकता नहीं है।

- डेटाबेस में सभी अनुक्रमणिका पर विखंडन जानकारी दिखाएं

SET NOCOUNT ON
USE pubs
DBCC SHOWCONTIG WITH ALL_INDEXES
GO

... पूरा होने पर एनओसीएटी वापस बंद करें

- एक टेबल पर सभी इंडेक्स पर विखंडन जानकारी दिखाएं

SET NOCOUNT ON
USE pubs
DBCC SHOWCONTIG (authors) WITH ALL_INDEXES
GO

- एक विशिष्ट सूचकांक पर विखंडन जानकारी दिखाएं

SET NOCOUNT ON
USE pubs
DBCC SHOWCONTIG (authors,aunmind)
GO

यह काम करेगा:

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 @[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

संपादित करें
यदि आप चाहते हैं, तो आप डेटा फ़िल्टर कर सकते हैं, यहां कुछ उदाहरण दिए गए हैं (ये विधि किसी भी विधि के लिए):

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%'

स्वीकृत उत्तर और दो अन्य प्रश्नों के आधार पर 1 , 2 मैंने निम्नलिखित क्वेरी को इकट्ठा किया है:

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

यह क्वेरी नीचे दिए गए परिणाम लौटाती है जो इंडेक्स, उनके कॉलम और उपयोग की सूची दिखाती है। यह निर्धारित करने में बहुत मददगार है कि कौन सी अनुक्रमणिका दूसरों की तुलना में बेहतर प्रदर्शन कर रही है:


Using SQL Server 2016, this gives a complete list of all indexes, with an included dump of each table so you can see how the tables relate. It also shows columns included in covering indexes:

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

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






reverse-engineering