How Big is the Database?

Posted in How Did I Do That?

So how big is Big Data? In this instance, the analysis server is fairly large:

 num_records   num_columns 
                       25,541,481,498                                         26,626

 

if object_id('tempdb..#eaa') is not null drop table #eaa;

CREATE TABLE #eaa(
    db [VARCHAR](100) NULL,
    table_name [VARCHAR](100) NULL,
    NumberOfRows bigint NULL,
    NumberOfColumns bigINT null,
)

GO

declare @cmd varchar(1500)
set @cmd='

BEGIN

use [?];
insert into #eaa
SELECT
    DB_NAME() AS db,
    object_schema_name(t.object_id) + ''.'' + t.name as table_name,
    i.Rows AS NumberOfRows ,
    COUNT(c.object_id) AS NumberOfColumns
from sys.tables t
INNER JOIN sys.sysindexes i ON t.OBJECT_ID = i.ID
inner join sys.columns c on t.object_id = c.object_id
where
indid IN (0,1)
and db_name() not in (''Utility'',''master'',''tempdb'',''msdb'')
and object_schema_name(t.object_id) not in (''temp'',''stage'',''audit'',''queue'',''import'',''export'')
GROUP BY object_schema_name(t.object_id) + ''.'' + t.name,i.rows
end
'
exec sp_MSforeachdb @cmd ;

delete FROM #eaa WHERE numberofrows=0;


SELECT * FROM #eaa ORDER BY db,table_name;
SELECT FORMAT(SUM(NumberOfRows),'N0') AS num_records , FORMAT(SUM(NumberOfColumns),'N0') AS num_columns
FROM #eaa;