Reindex tables individually without a cursor? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

Reindex tables individually without a cursor?

Author

Topic

surefooted
Posting Yak Master

188 Posts

Posted - 2004-10-06 : 11:03:19

Ok, I was wondering if there was a way to reindex a database's indexes table by table and indiviually in a script without using a cursor or a psuedo-cursor(while loop). Has anyone written something like this before? I have ahad suggetions here of generating an output file then executing it, but I would like to keep it all t-sql. Thanks in advance.

-Jon
Now a "Yak Posting Veteran".

X002548
Not Just a Number

15586 Posts

Posted - 2004-10-06 : 12:49:51

For some reason, this stops at 4000


DECLARE @SQL varchar(8000)
SELECT @SQL = ''

SELECT @SQL = @SQL + 'DBCC DBREINDEX ('''+TABLE_NAME+''''+') '
FROM INFORMATION_SCHEMA.Tables
SELECT LEN(@SQL), @SQL

EXEC(@SQL)

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-06 : 13:33:08

why do you need to avoid a loop or a cursor in this case? I cannot think of any reason why that would not be acceptable.

- Jeff

surefooted
Posting Yak Master

188 Posts

Posted - 2004-10-07 : 08:14:50

Brett, I was going to do that, but what happens when your out grow your variable?

Jeff, because of a way of thinking here. I have written it loop based.

-Jon
Now a "Yak Posting Veteran".

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-10-07 : 08:50:12

There is YET ANOTHER undocumented command, DBCC DBREINDEXALL. I DO NOT recommend it though, as I've never gotten satisfactory performance from it (for all I know, it hangs and never comes back). The syntax is:

DBCC DBREINDEXALL('myDatabase')

DO NOT use this on a production server until you have THOROUGHLY tested it on a separate server that's not heavily used. I'll still recommend that you use regular DBREINDEX though, it has been much much faster for me.

BTW, generating the DBREINDEX script to a file and running it is perfectly OK, you can use the osql utility to execute the file. You can call it from T-SQL using xp_cmdshell.

surefooted
Posting Yak Master

188 Posts

Posted - 2004-10-07 : 08:58:23

Cool thanks Rob.

-Jon
Now a "Yak Posting Veteran".

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-07 : 09:10:57

i bet $5 he's going ti use it

Go with the flow & have fun! Else fight the flow

surefooted
Posting Yak Master

188 Posts

Posted - 2004-10-07 : 10:19:35

Nah I just wrote a batch file that uses the osql method he mentioned. I took the more complex challenge.

If anyone cares I can post it.

-Jon
Now a "Yak Posting Veteran".

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-07 : 10:24:18

post it.

Go with the flow & have fun! Else fight the flow

surefooted
Posting Yak Master

188 Posts

Posted - 2004-10-07 : 10:43:04

[code]@echo off
set server=%1
set db=%2
set uid=-U%3
set pwd=-P%4
if "%1"=="" goto ErrorHandler
if "%2"=="" set db=master
if "%3"=="" set uid=-E
if "%4"=="" set pwd=

echo Checking for existence of reindex view on server %server% database %db%...

osql -S %server% %uid% %pwd% -d %db% -l 1 -Q"if object_id('dbo.vw_DBCC_TABLE_REINDEX') is not null drop view dbo.vw_DBCC_TABLE_REINDEX"

if errorlevel 1 goto LoginFailure

echo Creating a view with dbcc reindex commands on server %server% database %db%...

osql -S %server% %uid% %pwd% -d %db% -Q"create view dbo.vw_DBCC_TABLE_REINDEX as select cmd = 'DBCC DBREINDEX (''' + db_name() + '.dbo.[' + so.name + ']'','

+ si.name + ', 0)' from sysindexes si inner join sysobjects so on si.id= so.id where objectproperty(so.id, 'IsMSShipped') = 0 and objectproperty(so.id,

'IsTable') = 1 and si.indid > 0 and si.indid < 255 and (si.status & 64)=0"

if "%uid%"=="-E" set uid=-T

echo Generating the final script for server %server% database %db%...
bcp %db%.dbo.vw_DBCC_TABLE_REINDEX out DBCC_TABLE_REINDEX.SQL -S %server% %uid% %pwd% -c

if "%uid%"=="-T" set uid=-E

echo Processing DBCC_TABLE_REINDEX script on %server% database %db%...
osql -S %server% %uid% %pwd% -d %db% -i DBCC_TABLE_REINDEX.SQL -h-1 -n -w 256 -o %db%_DBCC_TABLE_REINDEX.LOG

echo Check %db%_DBCC_TABLE_REINDEX.LOG for any errors!

goto end
:ErrorHandler
echo No server and/or database specified!
echo Execution returned Error Code %ERRORLEVEL%
goto end
:LoginFailure
echo Failed to login to %server%!
echo Execution returned Error Code %ERRORLEVEL%
:end
@echo on[/code]

Using previous batch files as a guide.

-Jon
Now a "Yak Posting Veteran".

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources