Index Defragmentation in SQL Server 2012

Question

I'm running different scripts from SQLFOOL & SQL Authorities an etc. but when I check the database I cannot see any defragmentation at all. I wrote a bit code which is not working, could you please give me a hand?

Declare @table_name Varchar (70);

Declare table_cursor Cursor for 
     select OBJECT_SCHEMA_NAME([OBJECT_ID])+'.' + NAME AS Tablenamee 
     from sys.tables

open table_cursor
Fetch next from table_cursor into @table_name

While @@fetch_status = 0
begin
    Alter index all on @table_name
      REBUILD WITH (FILLFACTOR=80, ONLINE=ON) 

    fetch next from table_cursor into @table_name
end 

Close table_cursor
deallocate table_cursor

Getting error

incorrect syntax near @table_name

Don't you need a dynamic SQL for `Alter index all on @table_name`? Is it a new SQL 2012 feature? — cha, Oct 29 '15 at 01:15
I honestly dont know how to write dynamic task in SQL. I will try to research on that — siavash, Oct 29 '15 at 02:06
Why don't you schedule a maintenance job where you rebuild all indexes, say, every week? There are no queries to write/debug/maintain that way. — Salman A, Oct 29 '15 at 06:29

score 0 · Answer 1 · answered Oct 29 '15 at 06:26

You surely need to consider a efficient solution then writing your own query. A better solution would be using this https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Declare @table_name as Varchar(70);
declare @sql as nvarchar(max);
DECLARE @ParmDefinition NVARCHAR(100);

Declare table_cursor Cursor for 
     select top 10 OBJECT_SCHEMA_NAME([OBJECT_ID])+'.' + NAME AS Tablenamee 
     from sys.tables

open table_cursor
Fetch next from table_cursor into @table_name

While @@fetch_status = 0
begin
    set @sql = N'Alter index all on ' + @table_name +'
      REBUILD WITH (FILLFACTOR=80, ONLINE=ON)';

      SET @ParmDefinition = N'@table_name varchar(70)';

      EXECUTE sp_executesql @sql, 
                              @ParmDefinition, 
                              @table_name = @table_name;

    fetch next from table_cursor into @table_name
end 

Close table_cursor
deallocate table_cursor

Index Defragmentation in SQL Server 2012

1 Answers1