- Name - the name of the table
- Rows - the number of rows in the table
- Reserved - amount of total reserved space for the table
- Data - amount of space used by the data for the table
- Index_Size - amount of space used by the table's indexes
- Unused - amount of usused space in the table
"While sp_spaceused can be used to return the space usage for a single table, more than likely we want a web page where we can view the space used for all tables in a database. There are a number of ways to accomplish this, but in short we need to execute sp_spaceused once for every table in the database. In order to accomplish that we could do one of two things:"
- Query the sysobjects table to get a list of tables in the database, then use a CURSOR to iterate through these table results one at a time, executing sp_spaceused for each one.
- Use the undocumented sp_MSforeachtable stored procedure, which takes in a command and executes that command against all of the user tables in the database.
"The sp_MSforeachtable stored procedure is one of many undocumented stored procedures tucked away in the depths of SQL Server. A list of these handy stored procedures can be found at SQL Server 2000 Useful Undocumented Stored Procedures. In short, you can use sp_MSforeachtable like so:"
EXEC sp_MSforeachtable @command1="command to run"
"In the command to run put a ? where you want the table name to be inserted. For example, to run the sp_spaceused stored procedure for each table in the database, we'd use:"
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
create table #spaceused (name varchar(128), rows bigint, reserved varchar(25), data varchar(25), index_size varchar(25), unused varchar(25))
EXEC
sp_MSforeachtable @command1="insert into #spaceused EXEC sp_spaceused '?'"select
* from #spaceuseddrop
table #spaceused
0 comments: