CREATE PROCEDURE usp_DataRowCounts AS

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
-- Declare local variables
EXEC master..sp_MSForeachdb '
USE [?]IF DB_ID(''?'')>4
BEGIN	

INSERT INTO DBMaint..tblTableData
EXEC sp_MSforeachtable 'sp_spaceused ''?''

END'
GO



CREATE TABLE tblTableData
 (vchTableName VARCHAR(100) NOT NULL,
  intRows INT NOT NULL,
  vchReservedSpace VARCHAR(100) NOT NULL,
  vchData VARCHAR(100) NOT NULL,
  vchIndexSize VARCHAR(100) NOT NULL,
  vchUnusedSpace VARCHAR(100) NOT NULL)

CREATE PROCEDURE usp_DataRowCounts AS

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
declare csr_db cursor for
select [name] from master..sysdatabases
where [dbid] >4

declare @cmd varchar(max)

declare @dbs varchar(256)
open csr_db
fetch next from csr_db into @dbs
while @@fetch_status = 0
BEGIN
   set @cmd = @dbs + '..sp_msforeachtable '' insert into DBMaint..tblTabledata(vchtablename,introws,vchreservedspace,vchData,vchIndexSize,vchUnusedSpace) exec sp_spaceused ''''?'''' '''
   exec(@cmd)
   set @cmd = 'update DBMaint..tblTabledata set vchdatabasename = ''' + @dbs + ''' where vchdatabasename is null'
   exec(@cmd)
   fetch next from csr_db into @dbs
END
close csr_db
deallocate csr_db

CREATE TABLE tblTableData
 (vchdatabasename varchar(256) NULL,
  vchTableName VARCHAR(100) NOT NULL,
  intRows INT NOT NULL,
  vchReservedSpace VARCHAR(100) NOT NULL,
  vchData VARCHAR(100) NOT NULL,
  vchIndexSize VARCHAR(100) NOT NULL,
  vchUnusedSpace VARCHAR(100) NOT NULL)

USE master
GO
SET STATISTICS IO, TIME OFF
SET NOCOUNT ON
SET IMPLICIT_TRANSACTIONS ON
GO
IF OBJECT_ID('dbo.spaceused', 'U') IS NULL
	BEGIN
		CREATE TABLE dbo.spaceused (
		DbName sysname DEFAULT(''),
		tblName sysname,
		Row_count INT ,
		Reserved VARCHAR(50),
		data VARCHAR(50) , 
		index_size VARCHAR(50),
		unused VARCHAR(50),
		PRIMARY KEY CLUSTERED (DbName, tblName)
		); 
	END
ELSE
	BEGIN
		--DROP TABLE dbo.spaceused
		TRUNCATE TABLE dbo.spaceused
	END
COMMIT
GO
DECLARE @Cmd VARCHAR(8000)
SET @Cmd = 'USE [?]; 

IF ''?'' NOT IN (''tempdb''
				--, ''master'', ''model'', ''msdb''
				)
BEGIN
--PRINT ''?''
DECLARE @InnerCmd VARCHAR(8000)
SET @InnerCmd = ''
	EXEC sp_spaceused '''''' + CHAR(63) + ''''''''
	
	INSERT INTO master.dbo.spaceused(tblName, Row_count,Reserved,data,index_size,unused) 
	EXEC sp_MSforeachtable @InnerCmd
	
	UPDATE master.dbo.spaceused SET DbName = ''?'' WHERE DbName = ''''
END
'

--PRINT @Cmd

--EXEC sp_MSforeachtable @Cmd
EXEC sp_MSforeachdb @Cmd


SELECT
    DbName
  , tblName
  , Row_count
  , CONVERT(BIGINT, REPLACE(Reserved, ' KB', '')) / 1024 AS MB_Reserved
  , CONVERT(BIGINT, REPLACE(data, ' KB', '')) / 1024 AS MB_data
  , CONVERT(BIGINT, REPLACE(index_size, ' KB', '')) / 1024 AS MB_index_size
  , CONVERT(BIGINT, REPLACE(unused, ' KB', '')) / 1024 AS MB_unused
FROM
    dbo.spaceused
WHERE Row_count > 0
ORDER BY
    DbName
  , MB_Reserved DESC
  , Row_count DESC

COMMIT