Count all tables, rows, and rows per table in a database

Quite often I receive requests like this:
How big is our database today? How big was it yesterday? Last month? Last year?
In a simple way non-technical people don’t want to know the gigabyte size of the SQL Server master data file or the transaction log file. Those metrics don’t mean anything to them. Instead, they want to know how many tables are in the database, how many rows are in the database (across all tables), and how many rows are in each table.

You can create a simple script for calculating these metrics.

TableName VARCHAR(255) ,
RecordCount INT

EXEC sp_MSforeachtable ‘INSERT #Counts (TableName, RecordCount) SELECT ”?”, COUNT(*) FROM ?’;

FROM #Counts;

SELECT SUM(RecordCount) AS [Rows]
FROM #Counts;

SELECT REPLACE(REPLACE(TableName, ‘[‘, ”), ‘]’, ”) AS TableName ,
RecordCount ,
CAST(CAST(100.0 * RecordCount / ( SELECT SUM(RecordCount) AS [Rows]
FROM #Counts
) AS DECIMAL(5, 2)) AS VARCHAR) + ‘%’ AS Weight
FROM #Counts
ORDER BY TableName ,
RecordCount DESC;

SELECT REPLACE(REPLACE(TableName, ‘[‘, ”), ‘]’, ”) AS EmptyTableName ,
FROM #Counts
WHERE RecordCount = 0
ORDER BY TableName ,
RecordCount DESC;


Here we simply count the records per table and load the results into a temporary table. From this we can get:

a. The number of tables in the database;
b. The number of rows in the database (across all tables);
c. The number of rows in each table; and
d. The empty tables (with no rows).

Above script does not take into consideration the fact that a table might contain a small number of records and still heavily “outweigh” other tables in terms of disk space usage.

For example, a table containing one record that stores a binary copy of a 4GB video is obviously “bigger” (in terms of disk space) than a table containing one hundred records that store mailing addresses in plain text.

When this is an important consideration you can use standard reports in SQL Server like “Disk Usage by Top Tables”, or execute queries like this:

SELECT AS TableName , AS IndexName ,
SUM(p.rows) AS RecordCount ,
SUM(a.total_pages) AS PageCount ,
SUM(a.used_pages) AS UsedPages ,
SUM(a.data_pages) AS DataPages ,
( SUM(a.total_pages) * 8 ) / 1024 AS DiskSpaceMB ,
( SUM(a.used_pages) * 8 ) / 1024 AS UsedSpaceMB ,
( SUM(a.data_pages) * 8 ) / 1024 AS DataSpaceMB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
AND i.object_id > 255
AND i.index_id <= 1
i.object_id ,
i.index_id ,
ORDER BY OBJECT_NAME(i.object_id);

And here is another useful script, when you need to query for the disk size of your data and log files.

SELECT Type, SUM(size) Size
FROM ( SELECT SUBSTRING(filename, LEN(filename) – 2, 3) Type ,
CAST(size / 128.0 / 1024.0 AS NUMERIC(20,2)) Size
FROM dbo.sysfiles ) T GROUP BY Type
SELECT @@servername AS ServerName ,
DB_NAME() AS DatabaseName ,
FROM ( SELECT Size DataSizeInGB FROM CTE WHERE Type = ‘mdf’ ) [Data]
( SELECT Size LogSizeInGB FROM CTE WHERE Type = ‘ldf’ ) [Log]

Happy Reading !

Posted in Technology | Leave a comment

Team Work

Hello Friends,
Please go through this presentation for understanding the importance of teamwork. You can implement it in your day to day activity and feel the difference.

Enjoy Reading !


Posted in Uncategorized | Leave a comment

Collection in WCF Service

As we know Collection is very much specific to .Net,  WCF does not expose it to the metadata of the service. WCF provides marshaling rules for collection.

Actually, Collections are exposed as array in Service metadata.

Let us understand it with the help of an example.


public interface IStudentService
IEnumerable<StudentInformation> GetStudentInfo(int studentId);

List<StudentInformation> GetStudentData();

public class StudentService : IStudentService
List<StudentInformation> Students = new List<StudentInformation>();

public IEnumerable<StudentInformation> GetStudentInfo(int studentId)
IEnumerable<StudentInformation> Student = from student in Students
where student.StudentId == studentId
select student;

return Student;

public List<StudentInformation> GetStudentData()
List<StudentInformation> Student = (from student in Students
select student).ToList();

return Student;

When we add this service reference at the client side, we can see the collection is exposed as array.

On Client-side, if we try to access returned value of the service method

StudentServiceClient objClient =  new StudentServiceClient();
List<StudentInformation> studentList = objClient.GetStudentData();

we will face following error:

Cannot Implicitly convert type ‘StudentService.StudentInformation[]’ to System.Collections.Generic.List<StudentService.StudentInformation>.

Since, we are returning collection from the service but at the client side we are getting array. There is a disparity at the service end (Collection) and client end (Array). To overcome from this issue, we need to change the setting while adding service reference. While adding service reference, click on “Advance” button. It will open “Service Reference Settings” window. On this window, choose appropriate option for Collection Type i.e. System.Collections.Generic.List option.


Now following code will run smoothly without any issue at the client side,

IEnumerable<StudentInformation> objStudent = objClient.GetStudentInfo(Convert.ToInt16(txtStudentId.Text));

List<StudentInformation> lstStudent = objClient.GetStudentData();

I hope this article will be useful to you. Thanks for reading. Happy Coding .

Posted in Uncategorized | Leave a comment

Serializing and Deserializing Objects…to and from…XML

How do we serialize objects to XML and deserialize the XML back into objects. Let us understand it with the help of an example.

public class MyClass

// school property
private int _Age;

public int Age
get { return _Age; }
set { _Age = value; }

// new school property
public bool Citizen { get; set; }

// there’s nothing wrong with using fields
public string Name;


/// <summary>
/// Serializes an object to an XML string
/// </summary>
/// <param name=”AnyObject”>Object to serialize</param>
/// <returns>XML string</returns>

public static string SerializeAnObject(object AnyObject)
XmlSerializer Xml_Serializer = new XmlSerializer(AnyObject.GetType());
StringWriter Writer = new StringWriter();

Xml_Serializer.Serialize(Writer, AnyObject);
return Writer.ToString();

/// <summary>
/// DeSerialize an object
/// </summary>
/// <param name=”XmlOfAnObject”>The XML string</param>
/// <param name=”ObjectType”>The type of object</param>
/// <returns>A deserialized object…must be cast to correct type</returns>

public static Object DeSerializeAnObject(string XmlOfAnObject, Type ObjectType)
StringReader StrReader = new StringReader(XmlOfAnObject);
XmlSerializer Xml_Serializer = new XmlSerializer(ObjectType);
XmlTextReader XmlReader = new XmlTextReader(StrReader);

Object AnObject = Xml_Serializer.Deserialize(XmlReader);
return AnObject;

Below is the sample code which demonstrate how to use thess functions.

protected void Button1_Click(object sender, EventArgs e)
// create and initialize an object
MyClass Test = new MyClass();

Test.Age = 18;
Test.Name = “Rashid”;
Test.Citizen = true;

//  Serialize it

String XML;
XML = MiscUtilities.SerializeAnObject(Test);

// Deserialize it
MyClass Test2;

Test2 = MiscUtilities.DeSerializeAnObject(XML, typeof(MyClass)) as MyClass;

// TODO:  Get a cup of Tea and bask in the glory of rock solid code.


Happy Learning !

Posted in Uncategorized | Leave a comment

Hello world!

Welcome to This is your first post. Edit or delete it and start blogging!

Posted in Uncategorized | 1 Comment

Birthday Party.

Hi 2 All,
First of all sorry for late in posting new entry in my blog. Yesterday i celebarated my b’lated birthday party with my team mates and other colleagues. Really, it was a great experience for me and a memorable moment in my life. I thanks to all my team mates Specially Archana Mam, Sachin, Anil, Ravi, Sanjay and Janmaya. Also thanks to all my colleagues who came in the party and make it memorable.
Right now there is a song coming in my mind from the movie "Yaadein".
Yaadein yaad aati hai
Yaadein yaad aati hai
Yeh yaadein kisi dil-o-jaanam ke
Chal jaane ke baad aati hain
Yaadein, yaadein, yaadein !


Once again thanks a lot in coming and attending my party .

Posted in Entertainment | Leave a comment

A Very Happy New Year.

 Hi 2 All ,

 May this New Year be filled

 with Sweeteness and Happiness

 in all that you do and may this

 Joy continues the whole year through.

Posted in messages | Leave a comment