| Author |
Topic |
|
vicki
Posting Yak Master
117 Posts |
 Posted - 2002-04-29 : 16:02:10
|
| Hi, I have the database that include 150 tables and now I want to do the re-indexes. My question is there any way to run one command but then it will rebuild all indexes instead of excute the one command for each table? DBCC DBREINDEX (TABLE, '', 80) this is just for one table only, how can I do all the tables within one database at the same time?thanks |
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-04-29 : 16:09:57
|
| You can either write your own cursor to loop through all the tables, or you can use undocumented procedure sp_MSforeachtable like so:exec sp_MSforeachtable @command1="dbcc dbreindex ('?') WITH NO_INFOMSGS"This will reindex all of your tables.---------------Strong SQL Developer wanted in the Boston area. Please e-mail if interested. |
 |
|
|
vicki
Posting Yak Master
117 Posts |
Posted - 2002-04-29 : 16:17:18
|
| Thanks for your respond. I can't find the SP :sp_MSforeachtable from BOLdbcc dbreindex ('?') WITH NO_INFOMSGS what is question mark for?thanks |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-04-29 : 16:56:14
|
| sp_MSforeachtable is undocumented (so you aren't going to find it in BOL). There is a bunch of places on the web that describe it though. http://www.swynk.com/sqlhome/tips/knighttsql2.aspTo answer your question, "?" acts as a placeholder for the tablename. When this stored proc loops through all the tables in the database, it issues the specified command, replacing "?" with the actual table name.---------------Strong SQL Developer wanted in the Boston area. Please e-mail if interested. |
|
|
|
vicki
Posting Yak Master
117 Posts |
Posted - 2002-04-30 : 09:45:41
|
| Thanks again for your respond.Ok based on your answer that "?" acts as a placeholder for the tablename, is that mean this SP just only excuted for one table at the time?, What I want is to excute all the tables in the database at the same time.I try to excute sp_MSforeachtable @command1="dbcc dbreindex ('?') WITH NO_INFOMSGS" without replace '?' by any table then I got no error but I got the message "DBCC execution completed. If DBCC printed error messages, contact your system administrator."Is that mean I did the right way?, please helpthanks |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-04-30 : 10:19:39
|
| Did you follow the link? Did you read the article? Did you search the web for other articles about sp_MSforeachtable to clear up any additional question?-or-Did you not follow the link? Did you you try sp_MSforeachtable for about 2 seconds? (It's pretty clear from you question which applies)One too many ppl on this list have given you cut and paste code, and now that's what you expect. How many thread do you start and follow the same pattern?I pity the fool that continues to do your work for you. Search this site and/or google for 'sp_msforeachtable', if you are still confused after reading the TON of info out there, you are beyond help on this issue. But please don't ask izaltsman or sqlteam to draft any further explanation of the proc; it's a total waste of time.Bottom line, this community is here for professionals to ask and answer question regarding sql. This is not your personal code-gen hot-line. I am not suggestion you stop asking question. I am suggestion you take suggestions and go research a solution on your own, rather than expecting cut and paste code everytime. This place is give and take . . . you are all take and no give . . . get it?<O> |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-04-30 : 10:34:19
|
| Vicki,I hope you understand the frustration Page is conveying. He's not the only one who feels that way. This forum ought to be an exchange of ideas and solutions to and from SQL Server professionals. I emphasize professional because that word implies several preconditions, none of which you meet as far as I can see.First, you don't fully read explanations given by others. In this thread for example, izaltsman tells you sp_MSforeachtable is undocumented, yet your next post complains that you can't find the procedure in the documentation. Read what others have taken time out of their day to write.Second, you don't take the time to consider suggestions given to you. As Page pointed out, you didn't set up any test scripts on your end to experiment with sp_MSforeachtable. It seems posting here is a reflex for you rather than trying something out yourself. Implement others' suggestions first and see what they do for you before coming back with more questions.Lastly, find yourself a quality off-line source of documentation. So many posters have recommended BOL to you that I won't; perhaps books or users groups or training classes or something else will work. Without building a base of knowledge for yourself, you'll keep coming back here time after time with the same basic level of questions. After a while, though, the responses might not be so forthcoming.setBasedIsTheTruepath<O> |
|
|
|
vicki
Posting Yak Master
117 Posts |
Posted - 2002-04-30 : 11:00:51
|
| If any one have a professional level like your guys (I hope) then I don't think they borther to ask the questions in this forum |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-04-30 : 11:27:06
|
Are you friggin kidding me?Allow me to, first, take a stab at a translation . . .quote:
If any one have a professional level like your guys (I hope) then I don't think they borther to ask the questions in this forum
"If someone is on the same professional level as you guys, I would hope they would not bother to ask question on this forum"Ok . . . off the top, 'professional' in the context I was using it (and I assume SetBased as well), refers not to skill level, but rather to work-ethic, dedication, resourcefullness and thirst for improvement. We can all be professional, yet be on different skill levels. Additionally, I think you'll find that the more experience you gain, the more interested you become in how you (skill) peers do things. There are very few people here who are done learning.Secondly, we are saying 'read what people post and make the effort to process it'. Your post implies that you feel you have the 'right to ask questions'. knock, knock . . . anybody home? I said...quote:
I am not suggestion you stop asking questions.
The beef here is not with your questions . . . its with how you deal with the suggestions given too you . . . you expect someone to hold your hand and spells it out for you.It boggles the mind as to how in response to...quote:
...you don't fully read explanations given by others...
you post what you did.<O>Edited by - Page47 on 04/30/2002 11:27:35 |
|
|
|
|
Index