Reindex all indexes online or offline depending on day of week.

setnocount on 
declare@x int
declare @c varchar(500)
declare @dow varchar(80)
declare @tables table (id int identity(1,1), obj_id varchar(255), table_name varchar(255))
declare @indexes table (id int identity(1,1), table_id int, index_name varchar(255))
declare @worker table (id int identity(1,1), table_name varchar(255), index_name varchar(255), start_time datetime, end_time datetime)

----set day of week (@dow) to do full online=off reindexing
select  @dow = 'Sunday'
----get all tables that aren't just heaps
insert@tables (obj_id, table_name)
selectdistinct 
a.object_id, '[' + schema_name(a.schema_id) + '].[' + object_name(a.object_id) + ']'
fromsys.tables a
joinsys.indexes b 
ona.object_id = b.object_id
andb.name is not null

----get all indexes that aren't heaps
insert@indexes (table_id, index_name)
selecta.id,
b.name
from@tables a
joinsys.indexes b
ona.obj_id = b.object_id
whereb.name is not null
orb.type_desc <> 'HEAP'

if(select datename(dw, getdate())) <> @dow
begin
deletez
fromsys.tables a
joinsys.columns b
ona.object_id = b.object_id 
joinsys.indexes c
ona.object_id = c.object_id
joinsys.types d
onb.system_type_id = d.system_type_id
andb.user_type_id = d.user_type_id 
join@tables y
ona.object_id = y.obj_id
join@indexes z 
ony.id = z.table_id 
andc.name = z.index_name
where(c.type_desc = 'CLUSTERED')
and((b.system_type_id = 34 and b.user_type_id = 34)
or(b.system_type_id = 35 and b.user_type_id = 35)
or(b.system_type_id = 99 and b.user_type_id = 99)
or(b.system_type_id = 241 and b.user_type_id = 241)
or(b.system_type_id = 231 and b.user_type_id = 231 and b.max_length = -1)
or(b.system_type_id = 167 and b.user_type_id = 167 and b.max_length = -1)
or(b.system_type_id = 165 and b.user_type_id = 165 and b.max_length = -1))

deletez
fromsys.tables a 
joinsys.columns b 
ona.object_id = b.object_id  
joinsys.indexes c 
ona.object_id = c.object_id 
joinsys.index_columns d 
ona.object_id = d.object_id
andb.column_id = d.column_id
andc.index_id = d.index_id
join@tables y
ona.object_id = y.obj_id
join@indexes z 
ony.id = z.table_id 
andc.name = z.index_name
wherec.name is not null 
andc.type_desc <> 'CLUSTERED'
and((b.system_type_id = 34 and b.user_type_id = 34)
or(b.system_type_id = 35 and b.user_type_id = 35)
or(b.system_type_id = 99 and b.user_type_id = 99)
or(b.system_type_id = 241 and b.user_type_id = 241)
or(b.system_type_id = 231 and b.user_type_id = 231 and b.max_length = -1)
or(b.system_type_id = 167 and b.user_type_id = 167 and b.max_length = -1)
or(b.system_type_id = 165 and b.user_type_id = 165 and b.max_length = -1))

delete@tables 
whereid not in 
(select table_id from @indexes)

insert@worker (table_name, index_name)
selecta.table_name, b.index_name
from@tables a 
join@indexes b
ona.id = b.table_id 

select@x = max(id) from @worker
while@x > 0 
begin
update@worker
setstart_time = getdate()
whereid = @x

select@c = 'alter index ' + a.index_name + ' on ' + a.table_name + ' rebuild with (online=on)' 
from@worker a 
wherea.id = @x

exec(@c)

update@worker
setend_time = getdate()
whereid = @x

select@x = @x - 1
end
end
else
begin
delete@tables 
whereid not in 
(select table_id from @indexes)

insert@worker (table_name, index_name)
selecta.table_name, b.index_name
from@tables a 
join@indexes b
ona.id = b.table_id 

select@x = max(id) from @worker
while@x > 0 
begin
update@worker
setstart_time = getdate()
whereid = @x

select@c = 'alter index ' + a.index_name + ' on ' + a.table_name + ' rebuild' 
from@worker a 
wherea.id = @x

exec(@c)

update@worker
setend_time = getdate()
whereid = @x

select@x = @x - 1
end
end

select * from @worker