分享:查询索引碎片SQL调整
原查询索引碎片SQL如下:
SELECT object_name(dt.object_id) Tablename,si.name IndexName,
dt.avg_fragmentation_in_percent AS ExternalFragmentation,
dt.avg_page_space_used_in_percent AS InternalFragmentation FROM
( SELECT object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats (db_id(‘数据库名’),null,null,null,’DETAILED’ )
WHERE index_id <> 0) AS dt INNER JOIN sys.indexes si ON si.object_id=dt.object_id
AND si.index_id=dt.index_id
AND dt.avg_fragmentation_in_percent>10 AND
dt.avg_page_space_used_in_percent<75 ORDER BY avg_fragmentation_in_percent DESC
-------------------------------------------------------
以上SQL,存在一点小问题:
在SQL2008中附加SQL2000数据库,然后执行上面或下面SQL语句,会报错:')' 附近有语法错误。
select object_name([object_id]) 表名,* from sys.dm_db_index_physical_stats(db_id(),null,null,null,null)
请问这是为什么?
(在SQL2008中,直接新建数据库,然后执行上面SQL,是正常的)
--------------------------------------------------
针对上面问题,
本人对最上面的SQL语句做了两次调整,问题解决了,SQL如下:
declare @databaseName varchar(50),@dbid int
set @databaseName='barcodeXuTai'
select @dbid=db_id(@databaseName)
SELECT object_name(dt.object_id) 表名,
si.name 索引名,
dt.avg_fragmentation_in_percent AS 索引外部碎片,
dt.avg_page_space_used_in_percent AS 索引内部碎片
FROM
( SELECT object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent
FROM master.sys.dm_db_index_physical_stats(@dbid,null,null,null,'DETAILED')
WHERE index_id <> 0
) AS dt INNER JOIN sys.indexes si ON si.object_id=dt.object_id
AND si.index_id=dt.index_id
AND dt.avg_fragmentation_in_percent>10
AND dt.avg_page_space_used_in_percent<75
ORDER BY avg_fragmentation_in_percent DESC