How do I re index tables in SQL - Inherrited database

Hi there,
I have inherrited a database design with all the Tables, Procedures, Jobs etc.
A suggestion has been made that I re index the tables as some of the data content has changed.  
I am a novice to SQL - How do I re Index tables?
Thanking you
Heather
habbothaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

htowntechyCommented:
--Script to automatically reindex all tables in a database

USE DatabaseName --Enter the name of the database you want to reindex

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

------------------------------------------------------------------------------------------------------------------------------------------------
If you do a reorganization on a table with a clustered index, any non-clustered indexes on that same table will automatically be rebuilt.

The script will automatically reindex every index in every table of any database you select, and provide a fill factor of 90%. You can substitute any number appropriate for the fill factor in the above script.

When DBCC DBREINDEX is used to rebuild indexes, keep in mind that as the indexes on a specific table are being rebuilt, that the table becomes unavailable for use by your users.
------------------------------------------------------------------------------------------------------------------------------------------------
http://www.sql-server-performance.com/tips/rebuilding_indexes_p1.aspx
0
neeraj523Commented:
hello

try this

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO
0
habbothaAuthor Commented:
thanks  guys.  But where about in the SQL database do i put the scripts?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Anthony PerkinsCommented:
>>A suggestion has been made that I re index the tables as some of the data content has changed. <<
You may not know this, but there may not be any need: The index is updated everytime the data changes.  It would be a nightmare otherwise.
0
LowfatspreadCommented:
are you concerned about the statistics that are maintained on the indexes which exist for your tables?

then they above responses cater for that scenario...

or do you mean that the way you access the tables has changed and you want to add or change indexes?
0
habbothaAuthor Commented:
Hi
The way i access the tables has change and some of the tables have been recreated.
Thanks Heather
0
Anthony PerkinsCommented:
>>The way i access the tables has change and some of the tables have been recreated.<<
There is no need to reindex.  You can update stats, but that is another story.

On the other hand, if it makes you feel better to re-index, more power to you.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LowfatspreadCommented:
what has changed in the way the data is accessed?

do you have a problem wiyth the performance of some queryies?

can you post examples?
0
habbothaAuthor Commented:
Thanks all for the help - I have gone for the option of reindex the database and the performance is much better.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.