June 2, 2008 at 9:09 am
hi,
I know the db_id=4 n object_id=373576369,index_id=0 which has 96.83% avg_fragmentation_in_percent. So I want to know in which table on which columnname this fragmentation exist
Thanks
June 2, 2008 at 9:18 am
You can look in sys.indexes to find the name of the index. You can then either script the view through SSMS, or join to sys.objects to find out what table the index is on. If you investigate a bit more you'll work out who to know what column(s) are involved - I'm afraid I don't know how to do that off the top of my head.
John
June 2, 2008 at 9:40 am
John Mitchell (6/2/2008)
You can look in sys.indexes to find the name of the index. You can then either script the view through SSMS, or join to sys.objects to find out what table the index is on. If you investigate a bit more you'll work out who to know what column(s) are involved - I'm afraid I don't know how to do that off the top of my head.John
You could use something like this:
select i.object_ID, i.name, ic.index_column_ID, ic.column_id,isc.column_name
from sys.indexes i
inner join sys.index_columns ic
oni.object_id=ic.object_id and
i.index_id=ic.index_ID
inner join
(select object_id(table_name) tblObj,*
from information_schema.columns) ISC
ON ISC.tblobj=i.object_id
and isc.ordinal_position=ic.column_ID
where i.object_ID=1013578649 --<<<--replace with your object ID
order by object_id,i.name,index_column_id
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 2, 2008 at 9:55 am
hi,
i did not get the data after executing this script,only column names displaying?
June 2, 2008 at 9:58 am
garyreeds (6/2/2008)
hi,I know the db_id=4 n object_id=373576369,index_id=0 which has 96.83% avg_fragmentation_in_percent. So I want to know in which table on which columnname this fragmentation exist
Thanks
Index_id = 0 implies a HEAP table ... which can't be defragmented unless you create a clustered index on it ( and optionally drop it afterwards if you don't want to change the current schema)
* Noel
June 2, 2008 at 10:10 am
garyreeds (6/2/2008)
hi,i did not get the data after executing this script,only column names displaying?
I thought you were asking which columns where involved in this index? This would tell you what indexes on what the index name and columns are for the indexes on that table (add isc.table_name to the above select if you don't know the table name).
Also - for what it's worth - db_ID=4 is MSDB, which is a system database. That usually entails that it's something you probably don't want to be mucking around with it....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply