Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

List Every Index - SQL Server

RSS
Modified on Sat, Oct 25, 2008, 11:22 PM by Administrator Categorized as SQL Server
The following T-SQL statement will list every index in the current database.

select
     TableName = o.name
    ,ObjectName = i.name

    ,ObjectType = case 
        when i.name in (
            select name
            from sysobjects
            where ObjectProperty(id, 'CnstIsClustKey') = 1
            ) then 'Primary Key' 
        when i.name in (
            select name
            from sysobjects
            where ObjectProperty(id, 'IsUniqueCnst') = 1
            ) then 'Unique Key' 
        when IndexProperty(i.id, i.name, 'IsUnique') = 1 then 'Unique Index'
--                + case IndexProperty(i.id,i.name,'IsClustered') 
--                  when 1 then ', Clustered' else ', Non-clustered' end
        else 'Non-unique Index' 
--                + case IndexProperty(i.id,i.name,'IsClustered') 
--                  when 1 then ', Clustered' else ', Non-clustered' end
        end

    ,ColumnName = c.name

from
    sysindexes i
    inner join sysobjects o on i.id = o.id
    inner join sysindexkeys k on i.indid = k.indid and i.id = k.id
    inner join syscolumns c on k.id = c.id and k.colid = c.colid

where 1=1
    and o.type = 'U'
    and i.name is not null
    and IndexProperty(i.id, i.name, 'IsStatistics') = 0
--    and o.name like 'z[_]DeadCode[_]%'

order by
     TableName
    ,ObjectName
    ,k.keyno

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.