• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1050
  • Last Modified:

syntax for sp_msforeachtable sql server 2005

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
Greens8301
Asked:
Greens8301
  • 4
  • 3
  • 2
2 Solutions
 
chapmandewCommented:
exec sp_msforeachtable 'alter index all on [?] rebuild'
0
 
BrandonGalderisiCommented:
actually, the ? will have the [schema].[table] so it's not necessary

exec sp_msforeachtable 'alter index all on ? rebuild'
0
 
Greens8301Author Commented:
what ? => substitued for

Thanks
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
chapmandewCommented:
? 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
 
BrandonGalderisiCommented:
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
 
chapmandewCommented:
or the ? could be anything if you write your own...

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

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
 
BrandonGalderisiCommented:
correct.
0
 
BrandonGalderisiCommented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now