| Author |
Topic |
|
fralo
Posting Yak Master
161 Posts |
 Posted - 2011-04-17 : 16:06:26
|
| Hi all,I'm researching how to re-index all databases. I have found two suggestions. First, this one theoretically handles every table for every database which would be very convenient if it works.DECLARE @SQL varchar(8000) -- if you use nvarchar for whack table names, change this to 4000SET @SQL = ''SELECT @SQL = @SQL + 'EXEC ' + NAME + '..sp_MSforeachtable @command1=''DBCC DBREINDEX (''''*'''')'', @replacechar=''*''' + Char(13)FROM MASTER..Sysdatabases where NAME !='tempdb'PRINT @SQLEXEC (@SQL)The second one uses a looping mechanism and runs for only one database. I would have to re-run for every DB.But my major problem right now is just trying to run this statement prior to doing the reindex.select * from sys.tables.It won't recognize sys.tables!!!Any idea of what's wrong? Any suggestions as well on reindexing, and whether the code I have above should work.Thanks so much for your help. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
fralo
Posting Yak Master
161 Posts |
Posted - 2011-04-17 : 16:49:48
|
| Thank you so much Tara! So all I have to do is just run the code in your second link above and it will reindex all tables within all databases on the server?Would I have to change anything within it to fit my particular needs, or is it ready to go as is? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
fralo
Posting Yak Master
161 Posts |
Posted - 2011-04-17 : 16:58:56
|
| Oh, I just realized that I need to run both of your links, don't I? I originally thought you were giving me a choice between the two. The first one compiles the proc on the database, and the second utilizes it.I greatly appreciate your help Tara. Thanks a lot. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Jahanzaib
Posting Yak Master
115 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Re-index all databases