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.
| Author |
Topic |
|
dit_guy
Starting Member
1 Post |
 Posted - 2011-06-20 : 22:25:57
|
| I am trying to use a nested cursor to reindex all tables in selected databases.Code sample DECLARE @Database VARCHAR(255) DECLARE @Table VARCHAR(255) DECLARE @cmd VARCHAR(1000) DECLARE @fillfactor INT SET @fillfactor = 90 BEGIN DECLARE DatabaseCursor CURSOR FOR SELECT name as [database name] FROM sys.databases WHERE name IN ('XXX','YYY','ZZZ') ORDER BY 1 OPEN DatabaseCursor FETCH NEXT FROM DatabaseCursor INTO @Database WHILE @@FETCH_STATUS = 0 BEGIN SET @cmd = 'DBCC CHECKDB (''' + @Database + ''', NOINDEX)' EXEC (@cmd) 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'' order by table_name' EXEC (@cmd) OPEN TableCursor FETCH NEXT FROM TableCursor INTO @Table WHILE @@FETCH_STATUS = 0 BEGIN 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 ENDThe seems to crash randomly. Sometimes all tables in XXX get reindexed, sometimes all tables in XXX and YYY get reimdexed. The next run maybe only tables in db XXX get reindexed.I dont understand what is happening here. Can someone tell me, or suggest another alternative, through T-SQL?Thank you in advance for your help |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-06-20 : 22:40:09
|
| Lots of better ways to do this.Check out Tara's Blog post on this. |
 |
|
|
|
|
|
Reindex DB tables