Sunday, August 16, 2009

Rebuilding Of Indexes

We need to re build the indexes of our tables after running a lot of transactions. This will improve the performance of the data retrievals. the following T SQL command will help you do that.


DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT

SET
@fillfactor = 90

DECLARE DatabaseCursor CURSOR FOR
SELECT
name FROM master.dbo.sysdatabases
WHERE name IN ('master') //Name of the database
ORDER BY 1

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN

SET
@cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName
FROM '
+ @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''

-- create table cursor
EXEC (@cmd)
OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN

-- if it is a SQL 2000 server use the below commented command
--DBCC DBREINDEX(@Table,' ',@fillfactor)

--
if it is a SQL 2005 server us this command
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@cmd)

FETCH NEXT FROM TableCursor INTO @Table
END

CLOSE
TableCursor
DEALLOCATE TableCursor

FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE
DatabaseCursor
DEALLOCATE DatabaseCursor



I was able to find this query from the following web site. Thanks guys for helping us.

http://www.mssqltips.com/tip.asp?tip=1367

No comments:

Post a Comment

Retrieving Calendar of a Bookable Resource in Dynamics

There are occasions where we need to retrieve working days and working times of a resource in Dynamics grammatically. This is quite possible...