Reindex script question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Reindex script question

Author

Topic

orlith
Starting Member

6 Posts

Posted - 2008-08-26 : 07:24:35

Hi

I apologize for the question i'll ask. It could be a newbie question but it's not really part of my knowledge...

I got a script (mostly found on Internet)to reindex all indexes in a database.
here it is :



--count records in Travail.dbo.Index_Log where Complete=0
if (Select count(pkIndex_Log) as Ind_Count from Travail.dbo.Index_Log where Complete=0 and Run_Type='Indexing') = 0
Begin
-----START INSERT-----
DECLARE @Cur_Run int 
SET @Cur_Run=(select isnull(max(Run_No),0) + 1 from Travail.dbo.Index_Log)

DECLARE @TableName sysname
DECLARE cur_reindex CURSOR FOR

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES

OPEN cur_reindex
FETCH NEXT FROM cur_reindex INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @IndexName sysname
DECLARE cur_reindexA CURSOR FOR 
SELECT i.name as index_name
FROM dbo.sysindexes i
WHERE id = object_id(@TableName) and i.indid > 0 and i.indid < 255
and (indexkey_property(object_id(@TableName), i.indid, 1, N'isdescending') is not null)
and (i.name is not null) and dpages>0
OPEN cur_reindexA
FETCH NEXT FROM cur_reindexA INTO @IndexName
WHILE @@FETCH_STATUS = 0
BEGIN

insert into Travail.dbo.Index_Log (Complete, Table_Name, Index_Name, Run_No,Run_Type) values(0, @TableName, @IndexName, @Cur_Run, 'Indexing')

FETCH NEXT FROM cur_reindexA INTO @IndexName
END
CLOSE cur_reindexA
DEALLOCATE cur_reindexA
FETCH NEXT FROM cur_reindex INTO @TableName
END
CLOSE cur_reindex
DEALLOCATE cur_reindex
-----END INSERT----- 
End
----------------------------------END Insert table values------------------
DECLARE @doTableName varchar(75), @doIndexName varchar(75), @dopkIndex_Log int
DECLARE cur_doindex CURSOR FOR
select Table_Name, Index_Name, pkIndex_Log from Travail.dbo.Index_Log where Complete=0 and Run_Type='Indexing'
OPEN cur_doindex
FETCH NEXT FROM cur_doindex INTO @doTableName, @doIndexName, @dopkIndex_Log
WHILE @@FETCH_STATUS = 0
BEGIN
update Travail.dbo.Index_Log set Start_Date=getDate() where pkIndex_Log=@dopkIndex_Log
DBCC DBREINDEX (@doTableName, @doIndexName, 0, sorted_data_reorg)
update Travail.dbo.Index_Log set End_Date=getDate(), Run_Type='Indexed',Complete=1 where pkIndex_Log=@dopkIndex_Log
FETCH NEXT FROM cur_doindex INTO @doTableName, @doIndexName, @dopkIndex_Log
END
CLOSE cur_doindex
DEALLOCATE cur_doindex

The purpose of this script is to list (and store in a table) all indexes for all tables in a DB then reindex them. My problem is that this script works only for the table owned by DBO and not for the others. It seems that the variable @tablename returns always dbo.xxxxxx...

Unfortunatly I can't change the owner of the tables.

SELECT i.name as index_name
FROM dbo.sysindexes i
WHERE id = object_id(@TableName) and i.indid > 0 and i.indid < 255
and (indexkey_property(object_id(@TableName), i.indid, 1, N'isdescending') is not null)
and (i.name is not null) and dpages>0

This code don't work if the owner is not dbo.

Is there some code I could add (or replace) to change that ?

Thans for all inputs.
Thanks

orlith
Starting Member

6 Posts

Posted - 2008-08-26 : 08:20:58

Hi
I found a solution ,perhaps not the perfect one :) but it works now.

I change :


SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES

into


SELECT TABLE_SCHEMA+'.'+TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources