Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Unanswered: 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!


    Code:
    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!!
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    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....

  3. #3
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    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"
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by ansonee
    ..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.
    Have you hugged your backup today?

  5. #5
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    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')

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •