May 3, 2010 at 5:48 am
Hi,
i want to retrieve all indexes from a database with the columns of database name,table name,index name,frag level,row count,index size,ignore dup key.
is the script that i wrote will give me the correct results?
THX
select db_name(a.database_id) as database_name,object_name(b.object_id)as table_name,b.name as index_name,a.avg_fragmentation_in_percent,c.rowcnt,c.reserved,b.ignore_dup_key from sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL,NULL) as a join sys.indexes as b on a.object_id = b.object_id and a.index_id = b.index_id
join sys.sysindexes c on a.object_id = c.id and a.index_id = c.indid
where b.name is not null
May 6, 2010 at 3:30 pm
Looks good to me.
May 6, 2010 at 5:17 pm
I would recommend not to use sys.sysindexes since it is a soon to be deprecated view. It will work currently but may not be in the next release of SQL server.
You can also get the row count via sys.dm_db_index_physical_stats if you don't use the "limited" mode that you are using. You can also get it through a join to the sys.partitions view.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply