March 30, 2011 at 4:47 am
Hi Gurus,
Am trying to rewrite the script below to show the column that will give me the table name that the index comes from , so if i use the wildcards %% to show all indexes and ORDER BY avg_fragmentation_in_percent desc i can easily go to the tables to rebuild the indexes or run a scipt on that particular table , also this will help me identify which tables are being fragmented regulary.
I cant seem to sdd this information into the script though , cant seem to find the correct value.
Any ideas greatly appreciated.
Thanks
Matt
USE DATABASE
go
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'%%'),
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
order by avg_fragmentation_in_percent desc
go
:w00t:
--Edit the database name and also the table name
-- Can use %% in table name to show all indexes.
:w00t::w00t:
March 30, 2011 at 5:08 am
Pass NULL as the objectID to get all tables. That's what the objectID function will be returning when you specify %%, unless you happen to have a table called %%. The objectID function can't take wildcards.
SELECT DB_NAME() AS DatabaseName, Object_name(a.object_id) AS TableName, a.index_id, name AS IndexName, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'Limited') AS ips
INNER JOIN sys.indexes AS i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
ORDER BY avg_fragmentation_in_percent desc
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 30, 2011 at 5:26 am
Thanks , not to be pedantic , the code was wrong on the Object_id and had to omit as Table_name(see below)
SELECT DB_NAME() as DatabaseName , Object_name(a.object_id), a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'Limited') AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
order by avg_fragmentation_in_percent desc
But your a legend , thanks again.
Matt
March 30, 2011 at 5:32 am
Why did you omit the AS TableName? It's just a column alias.
What happened is that I changed the table aliases (as a and b were meaningless) and forgot to change it there. The error in no way pointed at the column's alias.
SELECT DB_NAME() AS DatabaseName, Object_name(i.object_id) AS TableName, i.index_id, name AS IndexName, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'Limited') AS ips
INNER JOIN sys.indexes AS i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
ORDER BY avg_fragmentation_in_percent desc
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 30, 2011 at 6:32 am
Because when i execute this i get the following error:-
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "a.index_id" could not be bound.
Removing the AS TableName syntax removes this error message
Its SQL 2008 R2 by the way as well.
Thanks
matt
March 30, 2011 at 6:34 am
Sorry my bad , i looked at the code and it wasnt bounding i
So i chnaged it slightly
SELECT DB_NAME() AS DatabaseName, Object_name(i.object_id) AS TableName, i.index_id, name AS IndexName, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'Limited')
AS ips INNER JOIN sys.indexes AS i ON ips.object_id = i.object_id AND ips.index_id = i.index_id ORDER BY avg_fragmentation_in_percent desc
Thanks agian for all your help
Much Kudos
Matthew
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply