Find data size from syscolumns,sysobjectssys.partitions,columns

Posted on 2010-03-28
Medium Priority
Last Modified: 2012-05-09
The following query brings the table's # of rows, but what join is needed to bring the data size also?

select isc.TABLE_SCHEMA,so.name,SUM(sc.length) AS ROW_SIZE_IN_BYTES,sp.rows as TotRows
from sysobjects so join syscolumns sc on so.name = OBJECT_NAME(sc.id)
join INFORMATION_SCHEMA.COLUMNS isc on sc.name=isc.COLUMN_NAME and so.name=isc.TABLE_NAME
join sys.partitions sp on OBJECT_NAME(sp.object_id)=so.name
where so.type = 'U' GROUP BY so.name, isc.TABLE_SCHEMA,sp.rows;

Open in new window

Question by:anushahanna
  • 2
  • 2

Author Comment

ID: 28929747
If data size for the table can be derived, it will be nice to have the sum of all the index size also for that table.
LVL 57

Accepted Solution

Raja Jegan R earned 2000 total points
ID: 28936382
try this simple one:
create table temp ( name varchar(100), rows int, reserved varchar(100), data varchar(100), index_size varchar(100), unused varchar(100));
EXEC sp_MSforeachtable @command1=" insert into temp EXEC sp_spaceused '?'";
select * from temp;

-- drop table

drop table temp

Open in new window


Author Comment

ID: 28971915
LVL 57

Expert Comment

by:Raja Jegan R
ID: 28975043

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

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.

Join & Write a Comment

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

587 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question