March 14, 2012 at 6:01 am
Hi,
I need to know the object ids for all indexes in a table , so that i can join it with the index_physical_stats table so that i can get the index name, id , typ and the fragmentation level ... all in one go .
But , how do i get the object ids for indexes in a database ? Tried using sys.sysobject and sys.objects , dont hink that helped. Also , sys.indexes just dazed me .
A little help please ...
March 14, 2012 at 6:24 am
you need to join sys.dm_db_index_physical_stats to sys.sysindexes
the below is what i use to get all the indexes in all databases which have more than 8 pages, more than 0% fragmentation and are tables which are not heaps
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = REPLACE(
CAST(
(
SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +
'SELECT ' + CHAR(13) + CHAR(10) +
'DatabaseName = DB_NAME(DDIPS.database_id),' + CHAR(13) + CHAR(10) +
'SchemaName = SCHEMA_NAME(SO.uid),' + CHAR(13) + CHAR(10) +
'ObjectName = SO.name,' + CHAR(13) + CHAR(10) +
'IndexName = SI.name,' + CHAR(13) + CHAR(10) +
'IndexType = DDIPS.index_type_desc,' + CHAR(13) + CHAR(10) +
'AVGFragmentation = DDIPS.avg_fragmentation_in_percent' + CHAR(13) + CHAR(10) +
'FROM ' + CHAR(13) + CHAR(10) +
'sys.dm_db_index_physical_stats (db_id(), null,null,null,null) DDIPS' + CHAR(13) + CHAR(10) +
'INNER JOIN ' + CHAR(13) + CHAR(10) +
'sys.sysobjects SO' + CHAR(13) + CHAR(10) +
'ON' + CHAR(13) + CHAR(10) +
'SO.id = DDIPS.object_id' + CHAR(13) + CHAR(10) +
'INNER JOIN ' + CHAR(13) + CHAR(10) +
'sys.sysindexes SI' + CHAR(13) + CHAR(10) +
'ON' + CHAR(13) + CHAR(10) +
'SI.id = DDIPS.object_id' + CHAR(13) + CHAR(10) +
'AND' + CHAR(13) + CHAR(10) +
'SI.indid = DDIPS.index_id' + CHAR(13) + CHAR(10) +
'WHERE ' + CHAR(13) + CHAR(10) +
'DDIPS.avg_fragmentation_in_percent > 0' + CHAR(13) + CHAR(10) +
'AND' + CHAR(13) + CHAR(10) +
'DDIPS.page_count > 8' + CHAR(13) + CHAR(10) +
'AND' + CHAR(13) + CHAR(10) +
'DDIPS.index_type_desc <> ''HEAP''' + CHAR(13) + CHAR(10) +
'AND' + CHAR(13) + CHAR(10) +
'DDIPS.database_id = DB_ID('+CHAR(39)+name+CHAR(39)+'); ' + CHAR(13) + CHAR(10)
FROM sys.databases
FOR XML PATH('')
)
AS NVARCHAR(MAX)
),
' ',CHAR(13) + CHAR(10)
)
SELECT @sql= REPLACE(@SQL,'<>','<>')
SELECT @sql= REPLACE(@SQL,'>','=')
SELECT @sql = REPLACE(@SQL,'= 8','> 8')
--SELECT @sql
EXECUTE sp_executesql @sql
March 14, 2012 at 6:27 am
Have you looked up sys.indexes and sys.dm_db_index_physical_stats in books online? The object_id is the id of the table on which the index is defined and the index_id is the specific index on that table.
March 14, 2012 at 6:48 am
anthony.green (3/14/2012)
you need to join sys.dm_db_index_physical_stats to sys.sysindexes
sys.sysobjects and sys.sysindexes are deprecated, included only for backward compatibility with SQL 2000 and should not be used in any new development. Use sys.objects and sys.indexes instead.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 14, 2012 at 6:50 am
GilaMonster (3/14/2012)
anthony.green (3/14/2012)
you need to join sys.dm_db_index_physical_stats to sys.sysindexessys.sysobjects and sys.sysindexes are deprecated, included only for backward compatibility with SQL 2000 and should not be used in any new development. Use sys.objects and sys.indexes instead.
thanks Gail, will modify the script.
March 14, 2012 at 9:06 am
Lynn Pettis (3/14/2012)
Have you looked up sys.indexes and sys.dm_db_index_physical_stats in books online? The object_id is the id of the table on which the index is defined and the index_id is the specific index on that table.
BOL helped for sure.
Joining sys.objects and sys.indexes on object_id worked.
and then used the db_id and object_id parameters in the sys.dm_db_index_physical_stats() query ..
Thanks 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply