calculate schema size in sql server

how to calculate the size of each schema in sql server 2005?
jung1975Asked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
Here you go...this will give you a base resultset...you can query from this all you want:

      SELECT
            name = OBJECT_NAME (id),
            rows = convert (char(11), TheRowCount),
            reserved_KB = reservedpages * 8,
            data_KB = pages * 8,
            index_size_KB = CASE WHEN usedpages > pages THEN (usedpages - pages) ELSE 0 END * 8,
            unused_KB = CASE WHEN reservedpages > usedpages THEN (reservedpages - usedpages) ELSE 0 END * 8,
            schema_name = s.name
      FROM
      (
      SELECT id = object_id,
            reservedpages = SUM (reserved_page_count),
            usedpages = SUM (used_page_count),
            pages = SUM (
                  CASE
                        WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
                        ELSE lob_used_page_count + row_overflow_used_page_count
                  END
                  ),
            TheRowCount = SUM (
                  CASE
                        WHEN (index_id < 2) THEN row_count
                        ELSE 0
                  END
                  )
      FROM sys.dm_db_partition_stats p

      GROUP BY objecT_id
      ) a
      join sys.objects o On a.id = o.object_id
      join sys.schemas s On o.schema_id = s.schema_id
0
 
BrandonGalderisiCommented:
# of objects?  By type?  Size of Tables?  Size of indexes?

You have to provide more info.
0
 
chapmandewCommented:
schemas don't have a size..are you talking about the number of objects owned by a schema?
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
chapmandewCommented:
if so...


select * from sys.objects o
join sys.schemas s on o.schema_id = s.schema_id
where s.name = 'schemaname'
0
 
jung1975Author Commented:
I meant the size of tables own by a schema... can I get this number?
0
 
chapmandewCommented:
total rows of tables in the schema, or physical size?
0
 
jung1975Author Commented:
I 'd like to see both...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.