MSSQL - 인덱스 갱신

MSSQL - 인덱스 갱신

DECLARE @i int, @sql varchar(1000)
 DECLARE @tablename varchar(1000),@ownerName  varchar(1000)

SET @i = 1

DECLARE DB_Cursor CURSOR FOR 
  SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_SCHEMA, TABLE_NAME
 OPEN DB_Cursor

FETCH NEXT FROM DB_Cursor
 INTO @ownerName, @tablename
 WHILE @@FETCH_STATUS = 0
 BEGIN

 SET @sql = 'ALTER INDEX ALL ON ' + @ownerName + '.' + @tablename + ' REBUILD WITH (PAD_INDEX = ON, FILLFACTOR = 90) '
  EXEC (@sql)

 PRINT CONVERT(VARCHAR, @i) + '__' + @ownerName + '.' + @tablename + '............ OK'
  SET @i = @i + 1

 FETCH NEXT FROM DB_Cursor
  INTO @ownerName, @tablename

END

CLOSE DB_Cursor
 DEALLOCATE DB_Cursor