locked
Indexing Stored Proc for SQL Server 2000 RRS feed

  • Question

  • Hi all,

     

    I have this stored proc from BOL for sql server 2000. In order to use this stored proc. I have to execute this procedure separately against all my sql server 2000 databases .Ex: 100 databases, then run 100 times.

     

    But I want to have a procedure for "SQL Server 2000" which can go against all the "User Databases" and do Reorg (or) Rebuild based on the fragmentation level. So this way I dont have to execute the stored proc for each database we have.

     

    I believe someone should have done this work in the past for " SQL Server 2000" . Please share your valuable information on this.

     

    Thanks.

     

    Code :

     

    Code Snippet

    --*Perform a 'USE <database name>' to select the database in which to run the script.*/
    -- Declare variables
    SET NOCOUNT ON
    DECLARE @tablename VARCHAR(128)
    DECLARE @execstr VARCHAR(255)
    DECLARE @objectid INT
    DECLARE @indexid INT
    DECLARE @frag DECIMAL
    DECLARE @maxfrag DECIMAL

    -- Decide on the maximum fragmentation to allow
    SELECT  @maxfrag = 30.0

    -- Declare 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 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 FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS'
                        )
            FETCH NEXT FROM tables INTO @tablename
        END

    -- Close and deallocate the cursor
    CLOSE tables
    DEALLOCATE tables

    -- Declare cursor for list of indexes to be defragged
    DECLARE indexes CURSOR
        FOR SELECT  ObjectName,
                    ObjectId,
                    IndexId,
                    LogicalFrag
            FROM    #fraglist
            WHERE   LogicalFrag >= @maxfrag
                    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
    GO

     

     

    Wednesday, October 15, 2008 2:53 PM

Answers

  • There is a cool little SP from SQL 7.0 onwards called

     

    EXEC sp_Msforeachdb. You can say

     

    EXEC sp_Msforeachdb 'EXEC MYDBMAINTAINCESP'  and it will run this for each DB in your server.

     

    Thus take the script you have given above and wrap it in a SP and the run this SP with this sp_MSForEachdb.

     

    Have fun.

     

    Wednesday, October 15, 2008 4:34 PM

All replies

  • There is a cool little SP from SQL 7.0 onwards called

     

    EXEC sp_Msforeachdb. You can say

     

    EXEC sp_Msforeachdb 'EXEC MYDBMAINTAINCESP'  and it will run this for each DB in your server.

     

    Thus take the script you have given above and wrap it in a SP and the run this SP with this sp_MSForEachdb.

     

    Have fun.

     

    Wednesday, October 15, 2008 4:34 PM
  •  

    I dont wanted to post this in new post , so I am posting this here.

     

    Based on the suggestion ( Thanks for the idea) , I wrapped my code into Stored Proc and then run

     

    Code Snippet
    >> Exec SP_MsForEachdb  MyProcdure

     

    Question:

     

    Some of the databases in my server are 80 compatible ( SQL Server 2000) and other are 90 compatible ( SQL Server 2005 ). So I want to run this code only for those databases which are 80 compatilbe but not for databases which are 90 compatibility.

     

    I believe we can use sys.databases (or) sys.sysdatabases to find this , but not sure where should I check this .

     I am wondering where I can add this functionality so that this "SP_MsForEacdb " runs only for 80 compatilble databases.

     

    If someone can help... It will be appreciated.

     

    Thanks.

    Wednesday, October 15, 2008 5:55 PM
  • Hi i tried to make something your SP .. give it a go

     

    Code Snippet

    ALTER proc RebuildIndex

    AS

    Begin

    SET NOCOUNT ON

    if (@@VERSION LIKE '%10.0%' OR @@VERSION LIKE '%9.0%') --sql 2008 OR 2005 OR OTHER WITH COMPATIBITY LEVELS CHANGED TO 100 OR 90

    BEGIN

    PRINT 'THIS IS NOT A 80 CPMPATIBLE DB SO I AM NOT REBUILDING ANY INDEX'

    END

    ELSE

    BEGIN

    DECLARE @tablename VARCHAR(128)

    DECLARE @execstr VARCHAR(255)

    DECLARE @objectid INT

    DECLARE @indexid INT

    DECLARE @frag DECIMAL

    DECLARE @maxfrag DECIMAL

    -- Decide on the maximum fragmentation to allow

    SELECT @maxfrag = 30.0

    -- Declare 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 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 FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS'

    )

    FETCH NEXT FROM tables INTO @tablename

    END

    -- Close and deallocate the cursor

    CLOSE tables

    DEALLOCATE tables

    -- Declare cursor for list of indexes to be defragged

    DECLARE indexes CURSOR

    FOR SELECT ObjectName,

    ObjectId,

    IndexId,

    LogicalFrag

    FROM #fraglist

    WHERE LogicalFrag >= @maxfrag

    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

    END

    END

     

     

    EXEC SP_MsForEachdb 'EXEC RebuildIndex'

     

     

    let me know if it works for you
    Wednesday, October 15, 2008 10:04 PM
  • Your logic of checking server version works 100 percent if all 80 compatible databases are on SQL Server 2000 instance and 90 compatible databases on SQL Server 2005 intance.

     

    But here I have few of the 80 compatible databases on SQL Server 2005 instance (i.e.) for these databases , they will have @@version = 90 but compatibility level = 80.  So I think we need to check compatibility at database level but not at server level.

     

    Please throw some ideas here.

     

    Thanks.

    Thursday, October 16, 2008 2:43 PM
  • You can use sp_dbcmptlevel to check the version but again if you run this from a batch command it will thorw an error becasue it is made to run only as adhoc command. So sadly on run time there is no way to check DB compatibility level  as of my knowledge.

     

    Thursday, October 16, 2008 3:31 PM