SQL Server Performance

Cursor to rebuild indexes not working in a SP stored in master

Discussion in 'SQL Server 2008 General Developer Questions' started by Moonwalker2000, Jan 14, 2011.

  1. Moonwalker2000 New Member

    Hi there,
    I've created the following stored proc (was named sp_) in master to run a curser to rebuild indexes in any databases. But when I use a user database it only selects the user tables in master.
    What am i doing wrong?
    Thanks in advance.USE
    [master]GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    ALTER proc [dbo].[reindex_all_PJL]
    @DB
    sysname, @fillfactor INT = 90as
    set
    arithabort on;declare
    @TableName VARCHAR(255),@sql NVARCHAR(500)
    DECLARE TableCursor CURSOR FORSELECT OBJECT_SCHEMA_NAME([object_id]) + '.' + name AS TableName
    FROM sys.tables order by nameOPEN
    TableCursorFETCH
    NEXT FROM TableCursor INTO @TableNameWHILE
    @@FETCH_STATUS = 0BEGIN
    SET
    @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'print
    @sql--EXEC (@sql)FETCH
    NEXT FROM TableCursor INTO @TableNameEND
    CLOSE
    TableCursorDEALLOCATE
    TableCursor/*
    use ReportServer
    GO
    declare @db sysname
    set @db = db_name();
    EXEC master.dbo.reindex_all_PJL @db;
    */
  2. RamJaddu Member

    Hi There,
    Try this should work.. you are not using @db parameter in you code
    Cheers
    ===Code below
    USE
    [master]GO
    SET
    ANSI_NULLS ON GO
    SET
    QUOTED_IDENTIFIER OFF GO
    Alter proc [dbo].[reindex_all_PJL] @DB
    sysname, @fillfactor INT = 90as
    set
    arithabort on;declare
    @TableName VARCHAR(255),@sql
    NVARCHAR(500) Set @sql = 'DECLARE TableCursor CURSOR FOR
    SELECT S.Name +''.''+ T.Name
    FROM '+@db+'.sys.tables TInner join '+@db+'.sys.schemas S
    on T.schema_id = S.schema_id
    order by T.name'Exec
    (@sql)OPEN
    TableCursorFETCH
    NEXT FROM TableCursor INTO @TableNameWHILE
    @@FETCH_STATUS = 0BEGIN
    SET
    @sql = 'Use ' +@db +' ;ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'print
    @sqlEXEC
    (@sql)FETCH
    NEXT FROM TableCursor INTO @TableNameEND
    CLOSE
    TableCursorDEALLOCATE
    TableCursor
  3. satya Moderator

    Wow excellent, good to see your response here ...keep it up [:)].
    Also if you want to perform such conditional backup and optimization for your databases see http://sqlserver-qa.net/tags/dba/default.aspx for Olla Halengren's scripts.
  4. Moonwalker2000 New Member

    Thank you very much.
    I just find it strange that it works perfectly outside a proc but always reverts back to the db used to store the sp (in this case master).
    MW
  5. gdm New Member

    Hi, Unless you have some *really* special needs, I recommend Ola Hallengren's maintenance stored procedures: http://ola.hallengren.com/They are really nice. Why reinvent the wheel? Cheers.
  6. Moonwalker2000 New Member

    Yes, I hear what you are saying. Thanks for the link.
    MW
  7. RamJaddu Member

    Nothing wrong writing scripts ourself - that would give you more control. if you have spare time.
  8. satya Moderator

    True in the sense to control the process, but in the case of database optimization Olla's scripts are highly recommended...they are DBA saviours.

Share This Page