October 11, 2011 at 10:31 am
Hi All,
I am writing a cursor to capture the indexes that are fragmented and I am sending the output to a table called fragstats_local in DBA_Diagnostics database.
DECLARE @DatabaseName VARCHAR(255)
DECLARE @sql NVARCHAR(4000)
DECLARE TableCursor CURSOR FOR
SELECT name AS DBName FROM sys.databases where database_id in ('11','12','13','14','15','16','17','18','19','20')
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'USE ['+@DatabaseName+']
insert into DBA_Diagnostics.dbo.FragStats_local(DatabaseName,tablename,indexname,indextype,AvgPageFragmentation,PageCounts,InsertedDate)
SELECT
DB_NAME(DPS.DATABASE_ID) AS [DatabaseName]
,OBJECT_NAME(DPS.OBJECT_ID) AS TableName
,SI.NAME AS IndexName
,DPS.INDEX_TYPE_DESC AS IndexType
,DPS.AVG_FRAGMENTATION_IN_PERCENT AS AvgPageFragmentation
,DPS.PAGE_COUNT AS PageCounts, CONVERT (date, GETDATE()) as Date
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) DPS
INNER JOIN sysindexes SI
ON DPS.OBJECT_ID = SI.ID
AND DPS.INDEX_ID = SI.INDID where DPS.avg_fragmentation_in_percent>=25 and index_type_desc<>''HEAP'' and page_count>25
ORDER BY DPS.avg_fragmentation_in_percent DESC'
--PRINT(@sql)
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @DatabaseName
END
CLOSE TableCursor
DEALLOCATE TableCursor
Now I want to use the fragstats_local table and get the database name and its corresponding tables and generate the below code from a different cursor
'use ['@databasename+']'
'alter index all on '+@tablename+' rebuild with (fillfactor=80)'
The issue I am having here is, i am not able to pass both the databasename and its associated tablename as parameters at the same time. I want the second cursor to generate output like
use [database1]
alter index all on tablename1 rebuild with (fillfactor=80)
alter index all on tablename2 rebuild with (fillfactor=80)
alter index all on tablename3 rebuild with (fillfactor=80)
use [database2]
alter index all on tablename1 rebuild with (fillfactor=80)
alter index all on tablename2 rebuild with (fillfactor=80)
alter index all on tablename3 rebuild with (fillfactor=80)
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
October 11, 2011 at 10:35 am
In case you preffer a ready made solution...
The answer to your current question will also be in that script.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply