Post reply

Table sizes (data and indexes) for all databases

  • September 5, 2016 at 3:06 am

    #329936

    Comments posted to this topic are about the item Table sizes (data and indexes) for all databases

  • September 27, 2016 at 6:31 am

    #1903310

    Thanks for the script. I'll give it a try.

  • September 27, 2016 at 8:19 am

    #1903352

    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

    #1903570

    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

    #1903655

    Easy enough I guess, great! Thank you!!

  • September 28, 2016 at 8:04 am

    #1903657

    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