My Anna

Indias Biggest Engineering Education Community Enriching Students Minds with Latest Placement Papers, Study Materials, Fresher Jobs and Free E Books

A Unit of Aim Clear Technologies Ltd.
Search Study Materials, Jobs, Placement Papers, Interview Tips, Training Videos & More

Advertise with us

Board index > Departments > Computer Science > Visual Basic,Dot Net
Who is online



Post new topic Reply to topic  Page 1 of 1
  [ 1 post ] 
Print view   |Previous topic   |Next topic

sreenath
Super Moderator
Super Moderator
User avatar

Joined:
Wed Aug 26, 2009 10:01 pm
Beans: 1,227
In Bank: 14665
College: ESEC
Degree: M.Sc
Department: CSE
State: TN
Fav Quote: no pain no gain
 
#2
Fri Oct 02, 2009 8:18 am
Post subject: Use a Cursor to Reindex your tables Post
If you've done any heavy lifting in database programming, you have no doubt had to use a Cursor somewhere. As a database administrator, I don't really like them, but I think they are lifesavers in the right circumstances. Sometimes, there's just no way around them. This little script creates a cursor, iterates through it calling DBCC Reindex with variable names , deallocates the cursor and then updates the system statistics. It also takes advantage of Information_Schema, which if you aren't familiar with it, check out BOL and learn a little more about - it's another life saver.

CREATE PROCEDURE RedoStatsAndIndexes
AS

DECLARE @MyTable varchar(255)

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

FETCH NEXT FROM myCursor INTO @MyTable
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing Table: ' + @MyTable
DBCC DBREINDEX(@MyTable, '', 90)
FETCH NEXT FROM myCursor INTO @MyTable
END

CLOSE myCursor

DEALLOCATE myCursor
EXEC sp_updatestats


Enjoy!
_________________
sreenath@myanna.inImage

Profile E-mail
Offline

Display posts from previous:  Sort by  
Post new topic Reply to topic  Page 1 of 1
  [ 1 post ] 
Bookmark & Share
Board index > Departments > Computer Science > Visual Basic,Dot Net

Who is online

Users browsing this forum: No registered users and 0 guests

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron
Powered by phpBB Group.

phpBB SEO