Solved

syntax for sp_msforeachtable sql server 2005

Posted on 2008-10-31
9
1,035 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 60

Expert Comment

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

Expert Comment

by:BrandonGalderisi
ID: 22851087
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
ID: 22851094
what ? => substitued for

Thanks
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 60

Accepted Solution

by:
chapmandew earned 300 total points
ID: 22851130
? 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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22851131
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
ID: 22851164
or the ? could be anything if you write your own...

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

Author Comment

by:Greens8301
ID: 22851453

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
ID: 22851516
correct.
0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 200 total points
ID: 22851529
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql query help 15 52
SQL Job Hung 17 37
Need some alteration to below mention query 2 11
Using datetime as triggers 2 20
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

756 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