March 19, 2013 at 12:00 am
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
March 19, 2013 at 1:05 am
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
March 19, 2013 at 2:06 am
Hi,
thanks for the link.
Just for my knowledge, i believe i'm hitting syntax issue.
Anyone can help?
thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply