Results 1 to 6 of 6

Thread: dbcc dbreindex

  1. #1
    Join Date
    Jul 2004
    Posts
    268

    Unanswered: dbcc dbreindex

    Hi All,

    I have a following script:

    DECLARE @TableName sysname
    DECLARE cur_reindex CURSOR FOR
    SELECT table_name
    FROM information_schema.tables
    WHERE table_type = 'base table'
    OPEN cur_reindex
    FETCH NEXT FROM cur_reindex INTO @TableName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    PRINT 'Rebuilding indexes ' + @TableName + ' table'
    DBCC DBREINDEX (@TableName, ' ', 10)
    FETCH NEXT FROM cur_reindex INTO @TableName
    END
    CLOSE cur_reindex
    DEALLOCATE cur_reindex
    GO

    I added as a step in the job. However, when I run it I get an error message:

    Msg 2501, Sev 16: Could not find a table or object named 'table_1'. Check sysobjects. [SQLSTATE 42S02]

    When I run
    SELECT table_name
    FROM information_schema.tables
    WHERE table_type = 'base table'

    table_1 is the first on list. Any idea why I am getting this error message?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Inka

    I almost replied to your previous post with this but didn't bother. I doubt you'll produce something as good as Tara's script (scroll down and you'll see her sproc - there are more db maintenance ones too). As a minimum you should get some good pointers.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I would bet that dbo does not own table_1.

  4. #4
    Join Date
    Jul 2004
    Posts
    268
    MCrowley,

    You are right. It is user1 that owns that table. How can I fix the script so I can prefix the table name with user1?

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Concatenate with TABLE_SCHEMA + '.' field.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jul 2004
    Posts
    268
    Thank you very much for your help. It worked.

Posting Permissions

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