Visualizar el tamaño de una base de datos

CREATE PROCEDURE sp_rpttblspc (@dbname varchar(30) = null, @tblname varchar(500) = null)
as

/* Created BY : Umachandar Jayachandran (UC) */
/* Created ON : 15 April 1996 */
/* Description: Used TO CREATE a report OF tables & number of rows. */
/* Resources : https://www.umachandar.com/resources.htm */

SET nocount ON
DECLARE @tablename varchar(500), @cmdstr varchar(255), @stringa varchar(500)
SELECT @dbname = isnull(@dbname, db_name()), @tblname = isnull(@tblname , '') + '%'
IF db_id(@dbname) IS NULL
BEGIN
raiserror('Invalid DATABASE name was specified.', -1, -1)
return(1)
END
CREATE TABLE #tblspc
(name varchar(500), rows varchar(10), reserved varchar(20), data varchar(20),
index_size varchar(20), unused varchar(20))
set @stringa ='declare tbls CURSOR FOR SELECT name FROM [' + @dbname + '].dbo.sysobjects WHERE type = ''U'' AND name LIKE ''' + @tblname + ''''
PRINT @stringa
exec(@stringa)
OPEN tbls
while('FETCH IS OK' = 'FETCH IS OK')
begin
FETCH next FROM tbls INTO @tablename
IF @@fetch_status <; 0 BREAK
SELECT @cmdstr = 'use ' + @dbname + ' EXEC sp_spaceused ''[' + @tablename + ']'''
INSERT INTO #tblspc exec(@cmdstr)
IF @@error <;>; 0
BEGIN
DEALLOCATE tbls
raiserror('Fatal error, unable TO obtain space details FOR tables.', -1, -1)
return(1)
END
end
DEALLOCATE tbls
SELECT name AS "Table Name:", rows as "Number OF Rows:" ,
data AS "Data Size", index_size as "Index Size",
reserved AS "Allocated Size"
FROM #tblspc
--ORDER BY convert(int, rows) desc, 1
ORDER BY convert(int, substring(reserved,1,PATINDEX('% KB%',reserved))) desc, 1
return(0)
GO

Y seguidamente:

CREATE PROCEDURE sp_TablesSize @bd as varchar(50)='DBA' AS
declare @db as char(30)
if @bd is null or @bd = '' begin set @bd='DBA' end
Create table #mydbs (dbname char( 255), size char( 255), dbowner char( 255), dbid int,
crdate datetime, status char( 255), Comp_lvl char( 255))
insert #mydbs Exec sp_helpdb
declare db_cursor CURSOR FOR SELECT dbname from #mydbs where dbname = @bd
open db_cursor
fetch next from db_cursor into @db
while (@@fetch_status <;>; -1)
begin
exec sp_rpttblspc @db
fetch next from db_cursor into @db
end
deallocate db_cursor
Drop table #mydbs
GO