April 26, 2010 at 3:24 am
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
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
Hi
Thanks for that I'll get to work on it.
Keith
April 26, 2010 at 7:06 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply