USE [dba]
GO
CREATE TABLE [dbo].[AllTableHistory](
	[db] [sysname] NOT NULL,
	[schemaName] [sysname] NOT NULL,
	[tbl] [sysname] NOT NULL,
	[rows] [int] NOT NULL,
	[dt] [smalldatetime] NOT NULL,
	[reservedKB] [int] NULL,
	[dataKB] [int] NULL,
	[index_sizeKB] [int] NULL,
	[unusedKB] [int] NULL,
	 CONSTRAINT [pk_allTblHist_100] PRIMARY KEY CLUSTERED 
	(
		[db] ASC,
		[schemaName] ASC,
		[tbl] ASC,
		[dt] ASC
	)
)
GO

-- hold space used values
Create table #t (
	tbl sysname,
	rows int,
	reserved varchar(32),
	data varchar(32),
	index_size varchar(32),
	unused varchar(32)
)

declare @db sysname
declare @schema sysname
declare @tbl sysname

-- get all tables, schema, rowcounts
Declare c Cursor
Read_Only
FOR
	select name from sys.databases where database_id > 4
open c
fetch next from c into @db
while @@FETCH_STATUS = 0
begin
	EXEC('
			INSERT INTO AllTableHistory (db, schemaname, tbl, rows, dt)
			SELECT 	''' + @db + ''', s.name, t.name, max(rows), DATEADD(day, 0, datediff(day, 0, getdate()-1))
			FROM 	[' + @db + '].sys.[tables] t
			JOIN	[' + @db + '].sys.[partitions] p
			on		t.object_id = p.object_id
			JOIN	[' + @db + '].sys.[schemas] s
			on		s.schema_id = t.schema_id
			group by
					s.name, t.name
		')
	fetch next from c into @db
end
close c
deallocate c

-- get space used info
DECLARE c Cursor Read_Only
FOR
	SELECT db, schemaname, tbl from AllTableHistory WHERE dt = DATEADD(day, 0, datediff(day, 0, getdate()-1)) ORDER BY db
Open c
Fetch Next From c INTO @db, @schema, @tbl
while @@FETCH_STATUS = 0
begin
	truncate table #t
	insert #t
	exec (
	'use [' + @db + '];
	exec sp_spaceused ''' + @schema + '.' + @tbl + ''''
)

	update	AllTableHistory
	set		reservedKB = Convert(int, Replace(t.reserved, 'KB', '')),
			dataKB = Convert(int, Replace(t.data, 'KB', '')),
			index_sizeKB = Convert(int, Replace(t.index_size, 'KB', '')),
			unusedKB = Convert(int, Replace(t.unused, 'KB', ''))
	from	#t t
	join	AllTableHistory h
	on		t.tbl = h.tbl
	where	h.db = @db
	And		h.schemaName = @schema
	and		h.dt = DATEADD(day, 0, datediff(day, 0, getdate()-1))
	Fetch Next From c INTO @db, @schema, @tbl
end
close c
deallocate c

drop table #t

Create table #t (
	tbl sysname,
	rows int,
	reserved varchar(32),
	data varchar(32),
	index_size varchar(32),
	unused varchar(32)
)

Declare @tbl sysname
Declare c Cursor
read_only
for
	select name from sys.tables
	--select name from sysobjects where type = 'u'
open c
fetch next from c into @tbl
while @@fetch_status = 0
begin
	insert #t
	exec sp_spaceused @tbl
	fetch next from c into @tbl
End
Close c
Deallocate c
select * from #t
drop table #t

SET NOCOUNT ON

DECLARE @SpaceUsed table (
   TableName SYSNAME,
   TableRows INT,
   TableSize VARCHAR(10),
   DataSpaceUsed VARCHAR(10),
   IndexSpaceUsed VARCHAR(10),
   UnusedSpace VARCHAR(10)
  )

DECLARE 
  @tablename NVARCHAR(200),
  @tableschema NVARCHAR(200),
  @cmd NVARCHAR(200) 

DECLARE c1 CURSOR
  FOR SELECT table_schema, table_name FROM information_schema.tables WHERE table_type = 'base table'
  
OPEN c1
FETCH c1 INTO @tableschema, @tablename
WHILE @@fetch_status = 0
  BEGIN 
    SET @cmd = 'exec sp_spaceused[' + @tableschema + '.' + @tablename + ']'
    INSERT INTO @SpaceUsed
    EXEC sp_executesql @cmd

    FETCH NEXT FROM c1 INTO @tableschema, @tablename
  END

CLOSE c1
DEALLOCATE c1

SELECT * FROM @SpaceUsed ORDER BY TableName