Solved

syntax for sp_msforeachtable sql server 2005

Posted on 2008-10-31
9
1,028 Views
Last Modified: 2012-05-05
Can anyone give me a syntax for sp_msforeachtable sql server 2005

I want to use it in "alter index ... rebuild"

I could not find this proceedure in book on line
I'll appreciate the help

Thanks
0
Comment
Question by:Greens8301
  • 4
  • 3
  • 2
9 Comments
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
exec sp_msforeachtable 'alter index all on [?] rebuild'
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
actually, the ? will have the [schema].[table] so it's not necessary

exec sp_msforeachtable 'alter index all on ? rebuild'
0
 

Author Comment

by:Greens8301
Comment Utility
what ? => substitued for

Thanks
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 300 total points
Comment Utility
? is the name of the table (and schema in my case).  you can omit the []...it just bascially is a swap out of the table name when the statemetn is built
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
in the sp_msforeach(table/db) the ? represents the object

So for sp_msforeachtable it's [schemaName].[table_name]

for sp_msforeachdb it's [databasename]

0
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
or the ? could be anything if you write your own...

http://blogs.techrepublic.com.com/datacenter/?p=401
0
 

Author Comment

by:Greens8301
Comment Utility

exec sp_msforeachtable 'alter index all on ? rebuild'

just like this should work, right.
"?" is  a place holder , when the procedure runs iteratively, it substitutes different  table name iteratively

Thanks
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
correct.
0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 200 total points
Comment Utility
You may want to use this:

exec sp_msforeachtable 'print ''Rebuilding indexes for ?''; alter index all on ? rebuild'

It will provide output as to what it is doing.


0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Temporarily disable SQL Replication 7 21
Backup Job question 4 17
Sort by Month and Year - SQL 3 22
SQL Server memory Issue 7 73
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now