June 4, 2019 at 7:15 pm
I have close to a 1000 databases on this server. I want to get an idea of how large is the largest table in each database, name and number of rows. Is it possible to do without using sp_MSforeachdb and without cursor?
Thanks.
Likes to play Chess
June 4, 2019 at 7:54 pm
You're likely going to need a loop mechanic either way. You could, however, have a look at Aaron Bertrand's sp_foreachdb instead though.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 4, 2019 at 8:11 pm
foreachdb would be the easier - but the following will also give you what you need - although with big number of db's there may be a memory hit
Not much different from what you would do with foreachdb
declare @Sql nvarchar(max) = ''
declare @Sql2 nvarchar(max)
set @Sql2 =
'
insert into #bigtables
select top 1 ''--dbname--'' as databasename
, sc1.name + ''.'' + t.name as tablename
, sum(p.rows) as rowcounts
, sum(a.total_pages) as totalpages
from [--dbname--].sys.tables t
inner join [--dbname--].sys.schemas sc1
on t.schema_id = sc1.schema_id
inner join [--dbname--].sys.indexes i
on t.object_id = i.object_id
inner join [--dbname--].sys.partitions p
on i.object_id = p.object_id
and i.index_id = p.index_id
inner join [--dbname--].sys.allocation_units a
on p.partition_id = a.container_id
where i.index_id <= 1
group by sc1.name + ''.'' + t.name
, i.object_id
, i.index_id
, i.name
order by sum(a.total_pages) desc;
'
select @Sql = @Sql + replace(@Sql2, '--dbname--', name)
from sys.Databases
if object_id('tempdb..#bigtables') is not null
drop table #bigtables;
create table #bigtables
( databasename varchar(128)
, tablename varchar(257)
, rowcounts bigint
, totalpages bigint
)
print @Sql
exec sp_executesql @Sql
select *
from #bigtables
June 5, 2019 at 10:04 am
easy
exec sp_msforeachdb ' SELECT TOP 1 * FROM ?.dbo.sysindexes ORDER BY rowcount desc'
if you really want , you could stick an inner join on ?.dbo.sysobjects into it, so that you can see the table name
plus you might want to look at indexes too
' SELECT TOP 1 sum(reserved),o.name FROM ?.dbo.sysindexes I inner join ?.sysobjects on o.id=i.id group by o.name ORDER BY sum(reserved) desc '
a warning though, the sp_msforeachdb is not supported by Microsoft any more
MVDBA
June 5, 2019 at 10:20 am
a warning though, the sp_msforeachdb is not supported by Microsoft any more
I don't believe it has ever been "officially" supported, as it's an undocumented procedure. That's one reason why I suggested Aaron Bertrand's sp_foreachdb.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply