Redindexing - 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)

Redindexing

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-05-13 : 08:11:10

odette writes "I am trying to run a script to automatically run a reindex, but i am running into a problem because i have different DB owners in one instance. When you run the reindexing command, it defaults to dbo. any ideas?!?!"

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-13 : 08:17:54

Here's a piece of one of my procedures that does this.

SET ARITHABORT ON
--Record start time
PRINT 'START TIME: ' + CAST(GETDATE() AS VARCHAR(255))
--Declare needed variables.
DECLARE
@int_error_counter INT,
@int_max INT,
@int_counter INT,
@txt_sql NVARCHAR(4000),
@txt_name NVARCHAR(4000),
@txt_owner NVARCHAR(255),
@txt_db NVARCHAR(255)
--Declare table variable to hold table or procedure names.
DECLARE @tbl_names TABLE (
int_id INT IDENTITY(1,1) PRIMARY KEY,
txt_name VARCHAR(255),
txt_owner VARCHAR(255))
--Insert into table all user tables.
INSERT @tbl_names(
txt_name,
txt_owner)
SELECT
so.name,
su.name
FROM
sysobjects so
INNER JOIN sysusers su ON so.uid = su.uid
WHERE
so.xtype = 'U'
AND so.name NOT LIKE 'dt%'
--Set up loop to run DBCC commands against all user tables.
SELECT
@int_max = (SELECT MAX(int_id) FROM @tbl_names),
@int_counter = (SELECT MIN(int_id) FROM @tbl_names)
WHILE @int_counter <= @int_max
BEGIN
SELECT @txt_name = (
SELECT '[' + txt_owner + '].[' + txt_name + ']'
FROM @tbl_names
WHERE int_id = @int_counter)
--Reindex all user tables.
SELECT @txt_sql = 'DBCC DBREINDEX(''' + @txt_name + ''')'
PRINT @txt_sql
EXEC ( @txt_sql )
--Update the statistics on all user tables.
SELECT @txt_sql = 'UPDATE STATISTICS ' + @txt_name
PRINT @txt_sql
EXEC ( @txt_sql )
SELECT @int_counter = @int_counter + 1
END

It takes care of the different table owners.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources