SQL 2000 - Full reindex, repair script

Did you ever wanted to completly rebuild, repair, index, compute statistics a SQL 2000 database?
Here you go one, you are free to adapt it to newest SQL versions!

-- v2.5

USE master
DECLARE @vDATABASE sysname
DECLARE @vKILL_TIME int,  @vKILLCONNECTIONS int, @vSINGLEMODE int, @vREPAIR_CATALOG int, @vREPAIR int, @vREPAIR_TYPE varchar(255), @vREINDEX int, @vUPDATESTATISTICS int, @vSHRINK int
DECLARE @st datetime, @sti int, @vPROC varchar(255), @vOWNER varchar(255), @vUID nvarchar(255), @var5 int, @var6 int, @vTEMP varchar(255), @name sysname, @txt_db NVARCHAR(255)

-- Compulsory variables
SET @vDATABASE='YOUR_DATABASE_NAME_HERE'

-- Options variables
SET @vKILL_TIME=0
SET @vKILLCONNECTIONS=1
SET @vSINGLEMODE=1
SET @vREPAIR_CATALOG=1
SET @vREPAIR=1
SET @vREPAIR_TYPE='REPAIR_ALLOW_DATA_LOSS'
SET @vREPAIR_TYPE='REPAIR_FAST'
SET @vREPAIR_TYPE='REPAIR_REBUILD'
SET @vREINDEX=1
SET @vUPDATESTATISTICS=1
SET @vSHRINK=1
--SET @vOWNER='dbo'

-- Initialize variables
set @vPROC=0
set @var5=0
set @var6=0

if @vSINGLEMODE=1
BEGIN
print '.'
print 'Set multiuser database ['+@vDATABASE+']...'
EXEC sp_dboption @vDATABASE, 'single user', 'false'
END -- @vSINGLEMODE

if @vKILLCONNECTIONS=1
BEGIN
print '.'
print 'Kill processes for database ['+@vDATABASE+']...'

print '.'

WHILE EXISTS (select * FROM master.dbo.sysprocesses WHERE db_name(dbid)=@vDATABASE and login_time
BEGIN
    SELECT TOP 1 @vPROC =spid from  master.dbo.sysprocesses where db_name(dbid)=@vDATABASE and login_time
    EXEC ('kill '+ @vPROC)
     PRINT ('Killed process number: ' + @vPROC)
    WAITFOR DELAY '000:00:01'
END

-- Show not sleeping!
select db_name(dbid),* from  master.dbo.sysprocesses where db_name(dbid)=@vDATABASE order by login_time
END -- @vKILLCONNECTIONS

if @vSINGLEMODE=1
BEGIN
print '.'
print 'Set singleuser database ['+@vDATABASE+']...'
EXEC sp_dboption @vDATABASE, 'single user', 'true'
END -- @vSINGLEMODE

if @vREPAIR_CATALOG=1
BEGIN
  print '.'
  print 'Repair database ['+@vDATABASE+'] catalog...'
  dbcc checkcatalog (@vDATABASE,@vREPAIR_TYPE) WITH ALL_ERRORMSGS
END

if @vREPAIR=1
BEGIN
  print '.'
  print 'Repair database ['+@vDATABASE+'] with '+@vREPAIR_TYPE+' option...'
  dbcc checkdb (@vDATABASE,@vREPAIR_TYPE) WITH ALL_ERRORMSGS
END

if @vREINDEX=1
BEGIN

select @txt_db = 'declare c1 cursor for (select name from '+@vDATABASE+'.dbo.sysobjects where xtype=''U'''
-- try to introduce OWNER
-- select top 1 @vUID=uid from @vDATABASE.dbo.sysusers where name like @vOWNER
-- if (@vOWNER<>'') and (@vUID>0)
--  BEGIN
--    set @txt_db = @txt_db+' and uid='+@vUID
--  END
set @txt_db =@txt_db+')'

exec(@txt_db)

open c1
fetch next from c1 into @name
WHILE @@FETCH_STATUS = 0
BEGIN
        set @var5=@var5+1
    fetch next from c1 into @name
END
close c1

open c1
fetch next from c1 into @name
WHILE @@FETCH_STATUS = 0
BEGIN

        set @st = getdate()

        set @var6=@var6+1
    print '.   '+CAST(@var6 AS char(4))+' /  '+CAST(@var5 AS char(4))
        
    print 'Reindexing... "'+@vDATABASE+'.dbo.'+@name+'"'
                SELECT @txt_db='DBCC DBREINDEX ('''+@vDATABASE+'.dbo.'+@name+''')'
    print '[SQL]: '+@txt_db
                EXEC(@txt_db)

    print 'Update statistics... "'+@vDATABASE+'.dbo.'+@name+'"'
    SELECT @txt_db='UPDATE STATISTICS '+@vDATABASE+'.dbo.['+@name+'] with FULLSCAN, ALL'
    print '[SQL]: '+@txt_db
                EXEC(@txt_db)

               set @sti=cast(datediff(ss,@st,getdate()) as int)
                print 'Batch completed in: ' + rtrim(CONVERT(varchar(6), @sti/3600)+ 'h:' + RIGHT('0' + CONVERT(varchar(2), (@sti % 3600) / 60), 2)+ 'm:' + RIGHT('0' + CONVERT(varchar(2), @sti % 60)+'s', 2)) + ' !'
    print '.'

    fetch next from c1 into @name
END
close c1
deallocate c1

END -- vREINDEX

if @vSHRINK=1
BEGIN
  print '.'
  print 'Shrinking database ['+@vDATABASE+']...'
  backup log @vDATABASE with no_log
  DBCC SHRINKDATABASE (@vDATABASE, 0)
END -- vSHRINK

if @vSINGLEMODE=1
BEGIN
print '.'
print 'Set multiuser database ['+@vDATABASE+']...'
EXEC sp_dboption @vDATABASE, 'single user', 'false'
END -- @vSINGLEMODE

print '.'
select 'Finished! Check messages tab!'