A Second Set of Eyes

This is more of a "does anyone see something I'm missing" post versus a real problem.

What I'm doing is modifying a script I found in BOL. The script iterates through all the tables in a database and performs a SHOWCONTIG on all the tables. For those tables at a certain level of fragmentation, it does an INDEXDEFRAG. What I'd like to add to this is a piece that will iterate through all databases as well.

I'm close but no cigar. I've posted the code below. If anyone has any insight into where I may be going wrong, it would be greatly appreciated!

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

SET NOCOUNT ON

DECLARE @.SQLSTRING VARCHAR(2000)
DECLARE @.DBNAME VARCHAR(64)
DECLARE @.tablename varchar(128)
DECLARE @.execstr varchar(255)
DECLARE @.objectid int
DECLARE @.indexid int
DECLARE @.frag decimal
DECLARE @.maxfrag decimal
DECLARE @.maxextfrag decimal

-- Decide on the maximum fragmentation to allow for.
SELECT @.maxfrag = 30.0
SELECT @.maxextfrag = 40.0

DECLARE db CURSOR FOR
SELECT [NAME]
FROM [master].[dbo].[sysdatabases]
WHERE [NAME] NOT IN
('master', 'model', 'msdb', 'tempdb')

-- Declare a cursor.
--DECLARE tables CURSOR FOR
-- SELECT TABLE_NAME
-- FROM INFORMATION_SCHEMA.TABLES
-- WHERE TABLE_TYPE = 'BASE TABLE'

-- Create the 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 db

-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

-- Loop through all the databases.
FETCH NEXT
FROM db
INTO @.DBNAME

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.execstr = 'USE ' + @.dbname + ';' + char(13)
PRINT @.execstr
EXEC (@.execstr)


-- Open the cursor.
OPEN tables

-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @.tablename

WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
WITH TABLERESULTS, ALL_INDEXES')
FETCH NEXT
FROM tables
INTO @.tablename
END

-- Close and deallocate the cursor.
CLOSE tables
DEALLOCATE tables

SELECT @.SQLSTRING = 'INSERT INTO DBA_ADMIN.Fragmentation
(DatabaseName,
RunDate,
ObjectName,
ObjectId,
IndexName,
IndexId,
Lvl,
CountPages,
CountRows,
MinRecSize,
MaxRecSize,
AvgRecSize,
ForRecCount,
Extents,
ExtentSwitches,
AvgFreeBytes,
AvgPageDensity,
ScanDensity,
BestCount,
ActualCount,
LogicalFrag,
ExtentFrag)
SELECT '
SELECT @.SQLSTRING = @.SQLSTRING + @.DBNAME
SELECT @.SQLSTRING = @.SQLSTRING + ', getdate(),
ObjectName,
ObjectId,
IndexName,
IndexId,
Lvl,
CountPages,
CountRows,
MinRecSize,
MaxRecSize,
AvgRecSize,
ForRecCount,
Extents,
ExtentSwitches,
AvgFreeBytes,
AvgPageDensity,
ScanDensity,
BestCount,
ActualCount,
LogicalFrag,
ExtentFrag
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
OR ExtentFrag >= @.maxextfrag'

PRINT @.SQLSTRING

EXEC(@.SQLSTRING)

FETCH NEXT
FROM db
INTO @.DBNAME
END

CLOSE db
DEALLOCATE db

-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
OR ExtentFrag >= @.maxextfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Open the cursor.
OPEN indexes

-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag

WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
' + RTRIM(@.indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@.frag)) + '%'
SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
' + RTRIM(@.indexid) + ')'
EXEC (@.execstr)

FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag
END

-- Close and deallocate the cursor.
CLOSE indexes
DEALLOCATE indexes
--
-- Delete the temporary table.
DROP TABLE #fraglist

Again, thanks!!there r quite a few problems
1) u cannot change the database context by executing dynamic sql exec('use dbname').
2) the cursor tables is opened outside the loop and closed inside the loop
3) the table Fragmentation is not defined anywhere
ther could be more...|||I addressed the issue withthe tables curosr - works fine now.

The table Fragmentation is actually a permanent table, not a temp table.

..is there any way to actually change database context via SQL besides doing an "in line"|||..is there any way to actually change database context via SQL besides doing an "in line"

Undocumented, but do a search on ms_foreachdb (and ms_foreachtable).

Regards,

hmscott

PS. Undocumented means undocumented, ymmv.|||no. even sp_msforeachdb will not change the context permanently. all that it will do is provide u an option to execute sql in a different db and for all db. the same can be done by

exec ('use mydb select * from mytable')