Exercise - 29 Oct 1999

Auditing a table

Brian Knight

October 29, 1999

1 Min Read
ITPro Today logo in a gray background | ITPro Today

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.

Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.

You May Also Like