SMO doesn't retrieve extended properties for index

Question

I'm trying to use SQL Server Management Objects (SMO) to retrieve an extended property on an index, but the retrieved SMO object has an empty ExtentedProperties collection. (The index is on a table.) The extended property is there, I checked in T-SQL. Also, extended properties, e.g. on the database object are found by SMO. All I'm doing is

Server s = new Server(<connectionObj>);
Database db = s.Databases[<databaseName>];
int extCount = db.Tables[<tableName>]
                 .Indexes[<indexName>]
                 .ExtendedProperties
                 .Count

To get

extCount == 0

Am I doing it wrong?

Cheers,

Tilman

PS: It's SQL Server 2005

Your code looks correct. Are you 100% sure your index actually has some extended properties defined? — Anthony Faull, Jul 07 '11 at 12:59
Thanks, I just double checked like so select * from fn_listextendedproperty ('MS_Description', 'SCHEMA', 'dbo', 'TABLE', 'T3_Index', 'INDEX', 'PK_T3_Index') and the property exists. — user812775, Jul 07 '11 at 13:47

score 4 · Accepted Answer · answered Aug 09 '11 at 14:30

You need to refresh the collections before you can reference their items by name - I know - its weird.

Try:

Server s = new Server(<connectionObj>);
Database db = s.Databases[<databaseName>];
db.Tables.Refresh();
db.Tables[<tableName>].Indexes.Refresh();
int extCount = db.Tables[<tableName>]
                 .Indexes[<indexName>]
                 .ExtendedProperties
                 .Count

score 0 · Answer 2 · answered Nov 07 '11 at 21:35

Your code is correct, I suspect your index type is IndexKeyType.DriPrimaryKey for that index and SMO for some strange reason fetches Extended Properties from the primary key object the index supports rather than from the index itself. You can see that if you run your code while in SQL Profiler.

SMO doesn't retrieve extended properties for index

2 Answers2