September 5, 2016 at 3:06 am
Comments posted to this topic are about the item Table sizes (data and indexes) for all databases
September 27, 2016 at 6:31 am
Thanks for the script. I'll give it a try.
September 27, 2016 at 8:19 am
This is very nice. Great work! I've been looking for a script to collect stats quickly across the server. The only problem with the scripts that I've come across is none of them seem to contain the schema name. Why is that? Does everyone normally just use dbo and nothing else?
September 28, 2016 at 2:59 am
This query include schema :
--sqlserver 2005 +
EXECUTE master.sys.sp_MSforeachdb
'USE [?];
select getdate() as snapdate,serverproperty(''MachineName'') svr,@@servicename sv, ''?'' _dbname,SchemaName=SCHEMA_NAME(uid), TableName= object_name(p.object_id),p.partition_id,p.partition_number,
lignes = sum(
CASE
When (p.index_id < 2) and (a.type = 1) Then p.rows
Else 0
END
),
''memory (kB)'' = cast(ltrim(str(sum(a.total_pages)* 8192 / 1024.,15,0)) as float),
''data (kB)'' = ltrim(str(sum(
CASE
When a.type <> 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END
) * 8192 / 1024.,15,0)),
''indexes (kb)'' = ltrim(str((sum(a.used_pages)-sum(
CASE
When a.type <> 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END) )* 8192 / 1024.,15,0)),p.data_compression,
p.data_compression_desc
from sys.partitions p, sys.allocation_units a ,sys.sysobjects s
where p.partition_id = a.container_id
and p.object_id = s.id and s.type = ''U'' -- User table type (system tables exclusion)
group by p.object_id,s.uid,p.partition_id,p.partition_number,p.data_compression,p.data_compression_desc
order by 3 desc'
;
September 28, 2016 at 8:01 am
Easy enough I guess, great! Thank you!!
September 28, 2016 at 8:04 am
you're welcome
Viewing 6 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