List of all index & index columns in SQL Server DB

stock index list
major stock indices in the world
major world indices live
types of stock market indices
s&p 500 index
wilshire 5000
world index list
stock exchange index names of different countries

How do I get a list of all index & index columns in SQL Server 2005+? The closest I could get is:

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

Which is not exactly what I want. What I want is, to list all user-defined indexes, (which means no indexes which support unique constraints & primary keys) with all columns (ordered by how do they appear in index definition) plus as much metadata as possible.

There are two "sys" catalog views you can consult:

select * from sys.indexes

select * from sys.index_columns

Those will give you just about any info you could possibly want about indices and their columns.

EDIT: This query's getting pretty close to what you're looking for:

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;

List of stock market indices, Meanwhile, investors of all types use indexes as performance proxies and In general, the Dow is known for its listing of the U.S. markets best  All World Indices. Indexes, Currencies, Commodities, Futures, Premarket, Economic Calender & more. Stock Market Live. Live Index. Live Chart. Buy Sell Signal,Stock

You can use the sp_helpindex to view all the indexes of one table.

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

And for all the indexes, you can traverse sys.objects to get all the indexes for each table.

Index List - Stock and Bond Indices, See the complete list of world stock indexes with points and percentage change, volume, intraday ^AORD, ALL ORDINARIES, 6,163.70, +21.40, +0.35%, 0. Columns index_name - index name columns - list of index columns separated with "," index_type Clustered index Nonclustered unique index XML index Spatial index Clustered columnstore index Nonclustered Clustered index Nonclustered unique index XML index Spatial index Clustered columnstore index

None of the above did the job for me, but this does:

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

If your names include spaces, add square brackets around them in the creation scripts.

When the last Key column is all nulls, you know that none are missing.

Filtering out primary keys etc as in the original request is trivial.

NOTE: Take care with this solution as it doesn't distinguish indexed and included columns.

An Introduction to U.S. Stock Market Indexes, All CFDs (stocks, indexes, futures) and Forex prices are not provided by exchanges but rather by market makers, and so prices may not be accurate and may  As list.index() returns the index of first occurrence of an item in list. So, to find other occurrences of item in list, we will call list.index() repeatedly with range arguments. We have created a function that uses list.index() and returns a list of indexes of all occurrences of an item in given list i.e.

--Short and sweet:

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]   

Major World Indices, We provide you with a full list of stock market indices as well as lists of the most Look at the top of the search results; Locate all of the different major indexes  How do I get a list of all index &amp; index columns in SQL Server 2005+? The closest I could get is: select s.name, t.name, i.name, c.name from sys.tables t inner join sys.schemas s on t.schema_i

The following works on SQL Server 2014/2016 as well as any Microsoft Azure SQL Database.

Produces a comprehensive result set that is easily exportable to Notepad/Excel for slicing and dicing and includes

  1. Table Name
  2. Index Name
  3. Index Description
  4. Indexed Columns - In order
  5. Included Columns - In order
 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;

Stock Market Index - Major World Indices, List of Equity Indexes. All Cap Equities (View All Cap Equities ETFs) · 2 for 1 Index · AlphaClone Hedge Fund Long/Short Index · AlphaFactor US Core Equity​  ALL_INDEXES. ALL_INDEXES describes the indexes on the tables accessible to the current user. To gather statistics for this view and the related views DBA_INDEXES and USER_INDEXES, use the SQL ANALYZE statement. Related Views. DBA_INDEXES describes all indexes in the database. USER_INDEXES describes the indexes owned by the current user.

Stock Indexes List, There are indices for almost every conceivable sector of the economy and stock market. Many investors are familiar with these indices through  These metrics are retrieved directly from Lucene, which Elasticsearch uses internally to power indexing and search. As a result, all document counts include hidden nested documents. To get an accurate count of Elasticsearch documents, use the cat count or count APIs

List of Equity Indexes, List of companies that develop stock market indices. List of Index Providers. There are 1,887 All data is a live query from our database. Build all files of a package distutils.command.build_clib: Build any C libraries in a package distutils.command.build_ext: Build any extensions in a package distutils.command.build_py: Build the .py/.pyc files of a package distutils.command.build_scripts: Build the scripts of a package

Market Indices, But in certain situations we are presented with tasks when we have more than once indices and we need to get all the elements corresponding to those indices. Lets discuss certain ways to achieve this task. Input : list = [9, 4, 5, 8, 10, 14] index_list = [1, 3, 4] Output : 4 8 10 Method #1 : Using List comprehension

Comments
  • check out my solutions, which use SQL Server code to get the info
  • was my solution what you needed?
  • The above solution is elegant, but according to MS, INDEXKEY_PROPERTY is being deprecated. See: msdn.microsoft.com/en-us/library/ms186773.aspx
  • Note that as user3101273 points out below, none of the answers include the index filter (filter_definition column from sys.indexes table).
  • Yep, I'm aware of these, but I cannot arrange all the required "sys." catalogs so that they will produce meaningful output.
  • New version is much better, but "and ind.is_unique = 0" is unneccessary: it filters out almost all required data. However, this query still includes too much system data, which I don't know how to get rid of.
  • @My-Name-Is: the OP wanted to get all user-defined indexes (is_ms_shipped=0), but no primary keys (is_primary_key=0) and no indexes that are created to support unique constraints only (is_unique_constraint=0).
  • Brilliant. Thank you, from this I can discover not only the clustered primary keys, as other solutions allowed (including the order of the column), but also if one of those columns is DESC not ASC! See in the output 'is_descending_key'
  • @ZainRizvi the WHERE (1=1) let him chain and reorder his AND ...s without worrying about which was first. See: stackoverflow.com/a/8149183/1160796 and stackoverflow.com/a/242831/1160796
  • Only problem with this is that it only includes the index key columns, not the included columns.
  • You need to also join on I.index_id = IC.index_id on the join to sys.index_columns
  • FYI: Above query cartesians columns across indexes