Tag Archives: sp_MSforeachtable

Disk Space – DBCC UPDATEUSAGE

Posted by on January 7, 2012 Comments Off on Disk Space – DBCC UPDATEUSAGE

One important tool in managing disk space is to make sure the system accurately reports space used by tables.  DBCC UPDATEUSAGE will update the values returned by sp_spaceused.

The output of sp_spaceused “displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.”  One thing to note when reviewing the output is you may see the the reserved size is disportionately larger than the data and index space used. The reserved space should always be greater, but in one case I did see > 20 GB of difference. The space reported by system can get out of whack (technical term) from repeated use of TRUNCATE statements and frequent DDL statements.

Excellent article on DBCC UPDATEUSAGE by Armando Prato.

Google search for DBCC UPDATEUSAGE.

Read more »

Disk Space – Get table sizes

Posted by on December 29, 2011 Comments Off on Disk Space – Get table sizes

Below is a script  I use to determine table size.  Basically, it walks through the user databases and for each table.  The information is converted in GB. It’s a pretty simple use of MS sp_spaceused with sp_msforeachtable. More info on sp_spaceused.

Full script, below. Download script here.

Read more »

For Each … SQL System Stored Procedures

Posted by on February 25, 2008 Comments Off on For Each … SQL System Stored Procedures

Sql server has some great built-in commands procs that can help you script server or database wide commands. sp_MSForEachTable and Sp_MSForEachDb

When first looking at a database , I sometimes like to run the following:
execute sp_MSforEachTable @command1=‘Print ”?”; Select Count(*) as [? – (count)] from ?;Select Top 10 * from ? ‘

I used the sp_MSforeachDB just the other day to set all the non-system
databases on a test server to Simple Recovery mode.Just made a quick stored procedure that I passed the ? value and filter out the system databases ( Master, Model, Tempdb and MSDB).

For More Information Checkout :

November 30, 2004
SQL Server Undocumented Stored Procedures sp_MSforeachtable and sp_MSforeachdb
By Gregory A. Larsen
http://www.databasejournal.com/features/mssql/article.php/3441031