April 4, 2012 at 3:56 am
Hi Guys,
I can run the script (without sp_MSforeachdb) in individual DB.
I have an instance with multiple DBs. As such, i need to loop my the script to run against all the DBs in the instance. However I have difficulty scripting it, keep have syntax error thrown out.
I guess it's due to the ' in the script. Can any kind soul help to take a look and advise?
thanks!
DECLARE @command varchar(1000)
SELECT @command = 'USE [?]
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @indexname VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
OPEN tables
FETCH NEXT
FROM tables
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END
CLOSE tables
DEALLOCATE tables
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag, indexname
FROM #fraglist
WHERE LogicalFrag >10
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
and countpages>1000
OPEN indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag, @indexname
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT '--Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
PRINT '--Table Name: ' + RTRIM(@tablename) + ', Index Name: ' + RTRIM(@indexname) + ')'
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',' + RTRIM(@indexid) + ')'
PRINT (@execstr)
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag, @indexname
END
CLOSE indexes
DEALLOCATE indexes
DROP TABLE #fraglist
GO'
EXEC sp_MSforeachdb @command
April 4, 2012 at 4:46 am
I would avoid DBCC commands for this task. Take a look at sys.dm_db_index_physical_stats instead.
If you insist doing this with DBCC, at least create a procedure to wrap the code before feeding it to sp_MsForEachDB, or you'll get crazy with escaping quotes properly.
-- Gianluca Sartori
April 4, 2012 at 4:53 am
Might need a bit of work to get it to how you want it, but the basis is the same
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
April 4, 2012 at 7:22 am
Apology guys. I'm using dbcc as i'm running it against my 2000 db. sorry to post it in the wrong section. anyone else has a better idea or script of check fragmentation against all dbs?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply