Exercise - 29 Oct 1999

You can audit a table and see index growth for all tables in a database (this example uses the Northwind database) without the use of cursors. Use the query

sp_MSforeachtable @command1="print '?'", @command2=
   "sp_MStablespace @name = '?'"

The result of the query is

\[dbo\].\[Employees\]
Rows DataSpaceUsed IndexSpaceUsed
----------- ------------- --------------
9 232 56
\[dbo\].\[Categories\]
Rows DataSpaceUsed IndexSpaceUsed
----------- ------------- --------------
8 112 40

If you want to use multiple commands, you can specify the commands by using @command1 through @command3. You can use up to three commands per query in sp_MSforeachtable.

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish