Rob's SQL Server Blog

Rob's SQL Server Blog


Helpful Blog

Performance Dashboard Reports for SQL Server 2008

Performance Dashboard Reports for SQL Server 2008

Reindex Tables

--sp_helpindex 'jm_wo_transaction'
declare @tblname nvarchar(100),@sqlstring nvarchar(2000)
set @tblname = 'memberflat'

IF OBJECT_ID('tempdb..#tmpindexes') IS NOT NULL
drop table #tmpindexes

IF OBJECT_ID('tempdb..#tmptableindexes') IS NOT NULL
drop table #tmptableindexes

create table #tmptableindexes
(index_name nvarchar(200), index_description nvarchar(1000), index_keys nvarchar(1000), table_name nvarchar(100))
create table #tmpindexes
(index_name nvarchar(200), index_description nvarchar(1000), index_keys nvarchar(1000))


--select * from information_schema.tables where table_type = 'BASE TABLE'
--DECLARE tmptable_name CURSOR FOR select table_name from information_schema.tables where table_type = 'BASE TABLE' and table_schema = 'dbo'
DECLARE tmptable_name CURSOR FOR select table_name from information_schema.tables where table_type = 'BASE TABLE' and table_schema = 'dbo' and table_name=@tblname
OPEN tmptable_name

FETCH NEXT FROM tmptable_name INTO @tblname

WHILE @@FETCH_STATUS = 0
BEGIN
print 'print '''+@tblname+''''
print 'go'
set @sqlstring = 'DBCC DBREINDEX (['+@tblname+'],'''',90)'
print @sqlstring
--exec sp_executesql @sqlstring
print 'go'
FETCH NEXT FROM tmptable_name INTO @tblname
end

CLOSE tmptable_name
DEALLOCATE tmptable_name