October 5, 2011 at 10:56 am
Hello,
I am using the below query to identify the indexes fragmented. Now I want to send the actual output of this query to a table in the database DBA_Diagnostics. I am putting this code in a job.
can anyone help me with this...Thanks.
DECLARE @DatabaseName VARCHAR(255)
DECLARE @sql NVARCHAR(4000)
DECLARE TableCursor CURSOR FOR
SELECT name AS DBName FROM sys.databases where database_id not in ('1','2','3','4','6','15','16','17','18','19')
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'USE ['+@DatabaseName+']
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
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
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
October 5, 2011 at 11:00 am
Take a look at the undocumented stored procedure. sp_msforeachdb
This will do what you want without the need for dynamic sql that you will encounter in this cursor.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 5, 2011 at 6:27 pm
I would create a temporary table before your cursor. I would then construnct an insert statement to insert into the results from the DMV into the temporary table. Then at the conclusion insert the values from that temp table into your table in your database. I've done this before if you need assistance with the sytanx, but you've done 90% of the work here.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply