August 26, 2008 at 3:54 am
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
August 26, 2008 at 6:22 am
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
August 26, 2008 at 6:22 am
Try replacing
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
with this
SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
_____________________________________________________________________
- Nate
August 26, 2008 at 6:26 am
Orlith (8/26/2008)
HiI found a solution ,perhaps not the perfect one 🙂 but it works now.
Functional always trumps elegance!
_____________________________________________________________________
- Nate
August 26, 2008 at 6:33 am
Nice ! I'll note it
thx 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply