Friday, May 29, 2009

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

Thursday, May 28, 2009

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
There was an error in this gadget
There was an error in this gadget