V_SCHEMA_TABLEINDEXES

Fields

Field	Field Type	Null
TableName	nvarchar(128)
IndexOrdinal	int
IndexName	nvarchar(128)	yes
IsClustered	bit	yes
IsUnique	bit	yes
IsPrimaryKey	bit	yes
HasFilter	bit	yes
FilterDefinition	nvarchar(max)	yes
IgnoreDupKey	bit	yes

Definition

Copy


                CREATE view [dbo].[V_SCHEMA_TABLEINDEXES]
                as

                    -- This view shows all indexes along with some interesting index attributes. Note that some of this routine was taken 
                    -- from SQL Profiler output

                    select
                        sys.tables.name [TableName],
                        sys.indexes.Index_id as [IndexOrdinal],
                        sys.indexes.name as [IndexName],
                        cast(case sys.indexes.index_id when 1 THEN 1 else 0 end as bit) as [IsClustered],
                        sys.indexes.is_unique as [IsUnique],
                        sys.indexes.is_primary_key as [IsPrimaryKey],
                        sys.indexes.has_filter as [HasFilter],
                        sys.indexes.filter_definition as [FilterDefinition],
                        sys.indexes.[ignore_dup_key] as [IgnoreDupKey]
                    from sys.tables
                    inner join sys.indexes on (sys.indexes.index_id > 0 and sys.indexes.is_hypothetical = 0) and (sys.indexes.object_id = sys.tables.object_id)
                    where (SCHEMA_NAME(sys.tables.schema_id) = N'dbo')