Navigation

	Home Page
	Index
	All Pages

Contributor Links

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

Other Wiki Sections

Software

List Every Index - SQL Server

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