Indexes & Replication - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Indexes & Replication

Author

Topic

beanz
Starting Member

35 Posts

Posted - 2005-02-23 : 06:09:48

I'm using the script below that I got from a SQL book (forgot the title) to rebuild the indexes on some of my databases.

Would running this on a replicated database cause any problems? Also, what does re-indexing actually do?

I'm pretty new to indexes/rebuilding etc so could someone look it over and let me know if it's any good!??

Many thanks,
Danny

/********************************************************************************/
/* */
/* Copyright by Author Sajal Dam, ISBN 1590594215 */
/* */
/********************************************************************************/

--Bring all databases to minimal-fragmentation for DBCC INDEXDEFRAG
SET NOCOUNT ON

--Create a temporary table to hold all table names in a database
IF(SELECT OBJECT_ID('tempdb..#Tables')) IS NOT NULL
DROP TABLE #Tables
GO
CREATE TABLE #Tables(
TableName sysname,
UserName sysname,
TableID INT PRIMARY KEY
)

--Process all databases
DECLARE @DBName sysname, @DBObjectName sysname,
@UserName sysname, @TableID INT
DECLARE DBNames CURSOR
FOR SELECT name FROM master.dbo.sysdatabases WITH(NOLOCK)
WHERE DATABASEPROPERTYEX(name,'Status') = 'ONLINE'
AND name = 'LFMCustDB'-- ('tempdb', 'model') --Database exclusion list
OPEN DBNames
FETCH NEXT FROM DBNames INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
--Identify all user tables in the database
-- PRINT CHAR(10) + 'Database: ' + @DBName
INSERT INTO #Tables
EXEC('USE ' + @DBName + ' SELECT name, USER_NAME(uid), id
FROM sysobjects WITH(NOLOCK)
WHERE xtype = ''U'' ORDER BY id')

--Process all the user tables in the database
SET @TableID = 0
SELECT TOP 1 @DBObjectName = TableName, @UserName = UserName,
@TableID=TableID
FROM #Tables WHERE TableID > @TableID
WHILE @@ROWCOUNT = 1
BEGIN
--Defragment the user table and all its indexes
-- PRINT 'Executing DBCC DBREINDEX(''' + @DBName + '.' + @UserName +
-- '.' + @DBObjectName + ''') ...'
EXEC('DBCC DBREINDEX(''' + @DBName + '.' + @UserName +
'.' + @DBObjectName + ''')')

SELECT TOP 1 @DBObjectName = TableName, @UserName = UserName,
@TableID=TableID
FROM #Tables WHERE TableID > @TableID
END

--Process the next database
TRUNCATE TABLE #Tables
FETCH NEXT FROM DBNames INTO @DBName
END
CLOSE DBNames
DEALLOCATE DBNames

SET NOCOUNT OFF
GO

beanz
Starting Member

35 Posts

Posted - 2005-02-23 : 11:28:45

Also, would this be good practice:

Backup database
Reindex (using posted SQL Statement)
Shrink files (dbcc shrinkfile ([filename])

Danny

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources