CREATE TABLE #temp
       (
       [name] nvarchar(128),
       [rows] char(11),
       [reserved] varchar(18),
       [data] varchar(18),
       [index_size] varchar(18),
       [unused] varchar(18)
       )
 
INSERT INTO #temp
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?' "
 
SELECT
       [name] AS TableName,
       CAST([rows] AS int) AS NumOfRows,
       CAST(REPLACE([reserved],' KB','') AS int) AS [TotalKB]
FROM
       #temp
ORDER BY 3 DESC
 
SELECT SUM(CAST(REPLACE([reserved],' KB','') AS int)) AS TotalKB_data FROM #temp
 
DROP TABLE #temp

USE [tempdb]
GO
-- Create Temp Table to collect data 
IF EXISTS    (SELECT * 
                FROM    tempdb.dbo.sysobjects 
                WHERE   id = OBJECT_ID(N'[tempdb].[dbo].[temp_CapturerSpaceUsed]')
                ) 
   DROP TABLE [tempdb].[dbo].[temp_CapturerSpaceUsed]
GO

CREATE TABLE [TEMPDB].[dbo].[temp_CapturerSpaceUsed](
    [Server] [nvarchar](128) DEFAULT @@SERVERNAME,
    [DatabaseName] [nvarchar](128) NULL,
    [TableSchema] [nvarchar](50) NOT NULL,
	[TableName] [nvarchar](128) NOT NULL,
	[Rows_cnt] [nvarchar](32) NOT NULL,
	[ReservedKB] [nvarchar](32) NOT NULL,
	[DataKB] [nvarchar](32) NOT NULL,
	[IndexSzKB] [nvarchar](32) NOT NULL,
	[UnusedKB] [nvarchar](32) NOT NULL,
	[Package_run_date] [datetime] DEFAULT CURRENT_TIMESTAMP
) ON [PRIMARY]
GO
--------------------------------------------------
-- Create Stored procedure to load data into table

IF OBJECT_ID(N'[dbo].[usp_CapturerSpaceUsed]') IS NOT NULL
   DROP PROCEDURE [dbo].[usp_CapturerSpaceUsed];
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [usp_CapturerSpaceUsed]
	@DatabaseName NVARCHAR(128)
AS
BEGIN
SET NOCOUNT ON;
---------------------------------------------------------------------------
---------------------------------------------------------------------------
-- Posts used as a references in creating this stored procedure
-- http://www.novicksoftware.com/Articles/sql-server-table-space-reporting-sp_spaceused.htm
-- http://www.sqlservercentral.com/scripts/Miscellaneous/30317/

DECLARE @TableSchema NVARCHAR(50)
DECLARE @TableName NVARCHAR(600)
DECLARE @SQL NVARCHAR(2000)

DECLARE @I AS INT
DECLARE @Row_Count AS INT
SET @I = 1

-- Get list of tabes in database \\
IF OBJECT_ID('tempdb..#TableList') IS NOT NULL DROP TABLE #TableList
CREATE TABLE #TableList (ID_num int IDENTITY(1,1),TableSchema NVARCHAR (50),TableName NVARCHAR (128) )     
INSERT INTO #TableList (TableSchema,TableName) EXEC ('SELECT TABLE_SCHEMA, TABLE_NAME FROM [' + @DatabaseName + '].INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE'' ORDER BY 1,2;')

-- Create table to capture resulst from sp_spaceused
IF OBJECT_ID('tempdb..#sp_spaceused') IS NOT NULL DROP TABLE #sp_spaceused
CREATE TABLE #sp_spaceused (TableName NVARCHAR (128) ,[Rows_cnt] [nvarchar](32),[ReservedKB] [nvarchar](32),[DataKB] [nvarchar](32),[IndexSzKB] [nvarchar](32),[UnusedKB] [nvarchar](32))

SET @Row_Count = (SELECT COUNT(*) FROM #TableList)

-- Cursorless loop 
WHILE @I <= @Row_Count 
	BEGIN
	
-- The replace is used to handle table names with apostophes
	SELECT @TableSchema = TableSchema, @TableName = REPLACE(TableName,'''','''''')	
      FROM #TableList WHERE ID_Num = @I;
-- Get sp_spaceused data
   SET @SQL = '[' + @DatabaseName + ']..sp_spaceused @objname=''[' + @TableSchema + '].[' + @TableName + ']''';
--   SELECT @SQL
   DELETE FROM #sp_spaceused ;
   INSERT INTO #sp_spaceused EXEC (@SQL);
-- Save sp_spaceused data with server, database, and table schema
INSERT INTO [tempdb].[dbo].[temp_CapturerSpaceUsed]
           ([Server]
           ,[DatabaseName]
           ,[TableSchema]
           ,[TableName]
           ,[Rows_cnt]
           ,[ReservedKB]
           ,[DataKB]
           ,[IndexSzKB]
           ,[UnusedKB]
           ,[Package_run_date])
        SELECT @@SERVERNAME, @DatabaseName, @TableSchema, TableName 
              ,[Rows_cnt],[ReservedKB],[DataKB],[IndexSzKB],[UnusedKB], current_timestamp
              FROM #sp_spaceused;
  
		SET @I = @I + 1
	END
DROP TABLE #TableList
DROP TABLE #sp_spaceused

END
GO
--------------------------------------------------
-- Process each database 
SET NOCOUNT ON
DECLARE @DBName NVARCHAR(128)

DECLARE db_cursor CURSOR FOR 
 SELECT name 
   FROM master.dbo.sysdatabases 
   WHERE name <> 'TEMPDB' 
    AND CONVERT(nvarchar(128),DATABASEPROPERTYEX(name,'Status')) = 'ONLINE'
   ORDER BY name
 
 OPEN db_cursor
 FETCH NEXT FROM db_cursor 
  INTO @DBName

WHILE @@FETCH_STATUS = 0
 BEGIN
   EXEC [usp_CapturerSpaceUsed] @DatabaseName = @DBName

   FETCH NEXT FROM db_cursor 
    INTO @DBName
END 
CLOSE db_cursor
DEALLOCATE db_cursor
GO
--------------------------------------------------
-- Cleanup
USE tempdb
GO
IF OBJECT_ID(N'[dbo].[usp_CapturerSpaceUsed]') IS NOT NULL
   DROP PROCEDURE [dbo].[usp_CapturerSpaceUsed];
GO

SELECT [Server]
      ,[DatabaseName]
      ,[TableSchema]
      ,[TableName]
      ,CAST ([Rows_cnt] AS decimal(18, 0)) AS [Rows_cnt]
      ,CAST (REPLACE([ReservedKB], ' KB','') AS decimal(18, 0)) AS [ReservedKB]
      ,CAST (REPLACE([DataKB], ' KB','') AS decimal(18, 0)) AS [DataKB]
      ,CAST (REPLACE([IndexSzKB], ' KB','') AS decimal(18, 0))  AS [IndexSzKB]
      ,CAST (REPLACE([UnusedKB], ' KB','') AS decimal(18, 0))  AS [UnusedKB]
      ,[Package_run_date] 
  FROM [tempdb].[dbo].[temp_CapturerSpaceUsed]
  
ORDER BY    [Rows_cnt] DESC