Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2010-06-17 : 06:59:01
|
Is there a way to report on Filtered Indexes?SSMS puts "Filtered" after the index attributes, so I assume there is a query that will do it, haven't been able to find one though.I removed all the filtered indexes I created (problems with QUOTED_IDENTIFIER) and missed one  so wanted to report if there are any more.Would be handy if there was a CREATE DATE on indexes too - that would enable me to diagnose WTF I did wrong! but I don't believe that exists either (except perhaps for Clustered Index in the associated SYSOBJECTS record) |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-06-17 : 08:16:30
|
| SELECT * FROM sys.indexes WHERE has_filter = 1orSELECT * FROM sys.indexes WHERE filter_definition IS NOT NULL--Gail ShawSQL Server MVP |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-06-17 : 08:17:59
|
| p.s. the clustered index does not have an associated row in sysobjects/sys.objects. Primary keys and unique constraints will. Please don't confuse the clustered index and the primary key.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-17 : 18:53:19
|
| I was thinking of the sysobjects entry (for the table) associated with the sysindexes entry but only valid when the table + clustered index created at the same time I suppose - unless a recreate clustered index re-dates sysobject.crdate (which seems unlikely)Thanks for the has_filter info. I looked in sys.sysindexes which got my nothing useful Minefield! sys.indexes very useful, ta. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-17 : 19:02:59
|
Found & Fixed a couple of filtered indexes I'd overlooked - NOW I realise why, their names are almost identical and I probably dropped & recreated the same one thinking it was the other Doh!All sorted now so the testers will be happy  |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-06-18 : 04:55:42
|
| Well sys.sysindexes won't have anything it it. It's a backward compatibility view for SQL 2000.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-18 : 05:41:20
|
| I obviously failed to register that when migrating and am still personally stuck in Compatibility Mode = 80 I went to BoL2008 and looked up sysindexes and checked all the columns in to see if anything would help ....BoL says that sysindexes is a "system table" - which was what I was expecting - but of course I didn't read the bit marked IMPORTANT and heavily highlighted that explained that sysindexes was for backwards compatibility only and going to be useless for my purpose Older and a bit wiser now ... and will synergistically use the new knowledge on other system views in future. Thanks for that Gail. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-06-18 : 10:55:15
|
quote:
Originally posted by Kristen
BoL says that sysindexes is a "system table" - which was what I was expecting
Which is interesting, because it's not. It's a view. The system table that it is based on is sys.sysidxstats (hidden and cannot be queried)--Gail ShawSQL Server MVP |
|
|
|
|
|
|
Find Filtered Indexes