EXEC sp_MSforeachdb @command

Question

EXEC sp_MSforeachdb @command

chewychewy

Hall of Fame

Points: 3126
More actions
March 19, 2013 at 12:00 am

#291245

Hi All,
Apology i'm quite new to commands.
I'm trying to loop the below SQL against all the DBs in the instance.
But getting tthe below error:
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near ' +ob.name +'.
Anyone can help?
thanks!
DECLARE @command varchar(1000)
SELECT @command = 'USE [?]
DECLARE @dbid int
SELECT @dbid = DB_ID()
SELECT name from sys.databases
where database_id=@dbid;
SELECT db.name dbname, ps.OBJECT_ID,ob.name table_name,
ps.index_id,b.name index_name, ps.page_count ,
ps.avg_fragmentation_in_percent,
CASE
when ps.avg_fragmentation_in_percent > 10 then 'ALTER INDEX "' + b.name + '" ON ' +ob.name +' REORGANIZE'
END AS 'action_to_take'
FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS ps,
sys.indexes as b,
sys.objects as ob,
sys.databases as db
where ps.OBJECT_ID = b.OBJECT_ID
and ps.object_id=ob.object_id
AND ps.index_id = b.index_id
and ps.database_id = DB_ID()
and ps.database_id=db.database_id
and b.name is not null
and ps.page_count>1000
and ps.avg_fragmentation_in_percent > 10
ORDER BY ps.OBJECT_ID'
EXEC sp_MSforeachdb @command

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

Why re-invent the wheel? The scripts provided at the link below will do what you need, will take only minutes to setup and are run all over the world by many thousands of users many times a day so the code is considered battle-tested and very reliable:

SQL Server Index and Statistics Maintenance

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

chewychewy Hall of Fame Points: 3126 More actions · Answer 2

Hi,

thanks for the link.

Just for my knowledge, i believe i'm hitting syntax issue.

Anyone can help?

thanks