SQL SERVER – Query to Find ByteSize of All the Tables in Database

SELECT CASE WHEN (GROUPING(sob.name)=1THEN 'All_Tables'
   
ELSE ISNULL(sob.name'unknown'END AS Table_name,
   
SUM(sys.lengthAS Byte_Length
FROM sysobjects sobsyscolumns sys
WHERE sob.xtype='u' AND sys.id=sob.id
GROUP BY sob.name
WITH CUBE

Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL Scripts, SQL Stored Procedure
Previous Post
SQL SERVER – Query to Display Foreign Key Relationships and Name of the Constraint for Each Table in Database
Next Post
SQL SERVER – Auto Generate Script to Delete Deprecated Fields in Current Database

Related Posts

15 Comments. Leave new

  • how i can find? please reply

    1.What is the current file system size of the database?

    2. What is the current size of the tables (used)?

    Reply
  • Good article Thank you :)

    Reply
  • Simple & sweet!

    Reply
  • great stuff…thanks

    Reply
  • sathish (@apshathish)
    November 1, 2011 12:15 pm

    i am the beginner.. so may i know what is byte_length…

    Reply
  • select a.name,Sum(b.[max_length]) from sys.objects a INNER JOIN
    sys.columns b ON a.object_id=b.object_id Where type = ‘u’ Group by a.name

    Reply
  • select a.name,Sum(b.[max_length]) from sys.objects a INNER JOIN
    sys.columns b ON a.object_id=b.object_id Where type = ‘u’ Group by a.name

    Reply
  • Saurabh Savaliya
    January 24, 2012 12:27 pm

    very helping article thanks buddy

    Reply
  • abhIShek BandI
    March 29, 2012 4:56 pm

    Hi Pinal,
    My table structure is like this.
    using with above script am getting the Max_Length as “3”

    Column_name Type Computed Length
    ID int no 4
    SPName varchar no -1

    But varchar(max) length is 8000bytes …
    How can we get exact lenght. Correct me if am wrong…

    Regards,
    abhIShek BandI

    Reply
  • Nice thank you for such a nice information

    Reply
  • Nice piece of information. As i am beginner as a DBA . I have started referring all your blogs from the beginning.

    Reply
  • Select ISNULL(t.name,’AllTables’)[TableName],Sum(C.max_length)[TableSize]
    from sys.tables T
    join sys.columns C on t.object_id=C.object_id
    Group by rollup (t.name)

    Reply
  • Select ISNULL(t.name,’AllTables’)[TableName],Sum(C.max_length)[TableSize]
    from sys.tables T
    join sys.columns C on t.object_id=C.object_id
    Group by Grouping Sets (t.name)
    —-for DBs having compatablilty Mode less than 100 ;)

    Reply

Leave a ReplyCancel reply

Exit mobile version