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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy