Find Schema Name, row size in bytes and Total Number of rows in table for all tables in SQL Server 2008

System catalog and views provided inbuilt with SQL Server, are really wonderful, very useful and handy to get some inside information immediately. Today I want to show you use of following different system catalog and views.
SysObjects: used to show you list of all objects in database
SysColumns: used to show you list of all columns of all tables
Information_Schema.Columns:  also going to show you the list of columns for all tables, some information are easy to access other than SysColumns but note that SysColumns is powerful than this one
Sys.Partitions: Generally tables and indexes are in at least one partition in SQL Server 2008 so I use it as a handy tool to get total number of rows in each table.

Now, here I present one very small code snippet which will show you Schema Name, Table Name, Total Maximuz size of row in table and total number of rows exits in table. I have used above introduced system catalog to get these information, You may have different combination to get this kind of information.
select
      isc.TABLE_SCHEMA as SchemaName,
      so.name as TableName,
      SUM(sc.length) AS RowSizeInBytes,
      sp.rows as TotRowsInTable
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;
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.