How Big is the Database?
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;
