Sunday, June 06, 2010

 
 
To check the space used by all tables in a database
 
USE master
GO
CREATE PROC dbo.sp_DBA_spaceused_AllTables
AS
SET NOCOUNT ON

-- Temporary table to catch the output from sp_DBA_spaceused
CREATE TABLE #T (
 TableName SYSNAME NOT NULL,
 NumberOfRows BIGINT NOT NULL ,
 Reserved BIGINT NOT NULL ,
 Data BIGINT NOT NULL ,
 IndexSize BIGINT NOT NULL ,
 Unused BIGINT NOT NULL 
)
 
-- We are only interested in user databases
IF DB_NAME() NOT IN ('master','model','msdb','tempdb','distribution')
 BEGIN
  DECLARE @UserTables TABLE(TableName NVARCHAR(256) NOT NULL)

  INSERT INTO @UserTables
  SELECT USR.name + '.' + OBJ.name
  FROM dbo.sysobjects AS OBJ
   INNER JOIN
   sys.schemas AS USR
   ON OBJ.uid = USR.schema_id
  WHERE OBJ.type='U'

-- loop through all tables without using a cursor.
  DECLARE @TableName NVARCHAR(256)
  SET @TableName=''
  WHILE @TableName IS NOT NULL
   BEGIN
    SELECT @TableName=MIN(TableName)
    FROM @UserTables
    WHERE TableName>@TableName

    IF @TableName IS NOT NULL
     BEGIN
      INSERT INTO #T
      exec sp_dba_spaceused @TableName
     END
   END
 END

-- return our records
SELECT
 DB_NAME() AS DatabaseName,
 GETDATE() AS DateCreated,
 TableName,
 NumberOfRows ,
 Reserved,
 Data,
 IndexSize,
 Unused

FROM #T

-- clean up after ourselves.
DROP TABLE #t
GO
After you finish creating this stored procedure, you can run against each database
 
exec master.dbo.sp_msforeachdb @command1="exec [?].dbo.sp_DBA_spaceused_AllTables"