Post reply

DBCC four part names with reindex

  • April 26, 2010 at 3:24 am

    #156903

    Hi

    I have a script the uses a cursor to fetch tablenames and then re-indexes a database. This has worked very well up to now.

    Our developers have started to create tables with schema name other than dbo e.g. 'WEB.Quicklinks'.

    My reindex script get the tablenames from SELECT TABLE_SCHEMA TABLE_NAME FROM information_schema.tables WHERE table_type = ''base table''' I've changed this to SELECT TABLE_SCHEMA +''.''+ TABLE_NAME AS TB FROM information_schema.tables WHERE table_type = ''base table''' but I now I get a DBCC error

    Unable to process object 'Test_DB' because it is a four-part name, which is not supported by any DBCC command.

    Anyone know how I can get around this

  • April 26, 2010 at 3:44 am

    #1157509

    I don’t think that DBCC DBReIndex supports working with schemas. DBCC DBReIndex was used in SQL Server versions that didn’t have schemas at all. The schemas were introduced with SQL Server 2005 and at that version the ALTER INDEX statement was also introduced. If you’ll check BOL (Books On Line), you’ll see at the article about DBCC DBReIndex that it will be removed in the next versions of SQL Server that that it is recommended to use Alter index instead of DBCC DBReIndex. In short you should modify your script to work with ALTER INDEX statement instead of DBCC DBReIndex.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • April 26, 2010 at 5:51 am

    #1157544

    Hi

    Thanks for that I'll get to work on it.

    Keith

  • April 26, 2010 at 7:06 am

    #1157578

    First, the advice to use ALTER INDEX is correct. But, just a point of clarification, DBCC DBREINDEX does support schema's. It just doesn't support the ability to run across databases. You need to switch your context to the database within which you want to operate in order to run it. But don't bother. Learn how to use ALTER INDEX.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply