by Andrei Hetel
26. March 2010 16:41
This particular script I consider very useful especially when you 'inherit' a database that need tuning. I pick the idea long time ago somewhere from the net and I simplify it as much as I could. It contains only a couple of lines. During the years was used against SQL Server 2000, 2005 and 2008. First, create a table:
CREATE TABLE _tmpspace
(
[name] SYSNAME,
[rows] INT,
reserved VARCHAR(50),
data VARCHAR(50),
index_size VARCHAR(50),
unused VARCHAR(50)
)
A stored procedure is needed:
CREATE PROCEDURE [dbo].[_TablesSpaceUsed]
AS
BEGIN
SET NOCOUNT ON;
DBCC updateusage(0) WITH NO_INFOMSGS
TRUNCATE TABLE _tmpspace
EXEC sp_msforeachtable 'INSERT _tmpspace EXEC sp_spaceused ''?'''
SELECT * FROM _tmpspace ORDER BY [name]
END
Please note the use of undocumented stored procedure msforeachtable. Another interesting procedure is sp_MSforeachdb, but I'll write about it some other time.