SQL Reindex

One way to speed up the system is to perform REINDEX, which recalculates and rebuilds all indexes in the database. The operation can be time consuming depending on the size of the database.

declare @tablename as varchar(32)
declare @indexname as varchar(100)
CREATE TABLE #tmpTables (qualitier varchar(100),owner varchar(100),tablename varchar(100), tabletype varchar(100), remarks varchar(1000))
CREATE TABLE #tmp (INDEXNAME varchar(100),field2 varchar(1000),field3 varchar(1000))

INSERT #tmpTables EXEC sp_tables
DECLARE Tmp2_Cursor CURSOR FOR SELECT tablename FROM #tmpTables where tabletype = 'TABLE'
OPEN Tmp2_Cursor
FETCH NEXT FROM Tmp2_Cursor INTO @tablename
WHILE (@@FETCH_STATUS <> - 1) 
BEGIN
      INSERT #tmp EXEC sp_helpindex @tablename
      DECLARE Tmp_Cursor CURSOR FOR SELECT INDEXNAME FROM #tmp
      OPEN Tmp_Cursor
      FETCH NEXT FROM Tmp_Cursor INTO @indexname
      WHILE (@@FETCH_STATUS <> - 1) 
      BEGIN
      DBCC INDEXDEFRAG( N'---------- SEM DEJ NAZEV DB -------------------', @tablename, @indexname )
        FETCH NEXT FROM Tmp_Cursor INTO @indexname
      END
      CLOSE Tmp_Cursor
      DEALLOCATE Tmp_Cursor
      DELETE FROM #tmp
      FETCH NEXT FROM Tmp2_Cursor INTO @tablename
END
CLOSE Tmp2_Cursor
DEALLOCATE Tmp2_Cursor
DELETE FROM #tmpTables
DROP table #tmp
DROP table #tmpTables