Find Largest Table Using SQL Server 2008

In this article I describe how to find largest table in a particular database.
  • 1842

Introduction

In this article I describe how to find largest table in a particular database. We can find largest table by exploring SQL Server database also but it will consume more time. One of the fastest way is: use undocumented SQL Server stored procedure, which quickly generates a list of tables which have most rows. In my previous article I explained about sp_foreachtable undocumented system stored procedure, you can visit from here: sp_MSforeachtable Stored Procedure in SQL Server 2008

Find Largest Table:

Create database

create database EmployeeDetails

go

use EmployeeDetails

First we create three tables in EmployeeDetails database.

Create first table:

create table emp(id int,name varchar(20))

go

insert into emp

select 1,'Henry' union all

select 2,'Neil' union all

select 3,'David'

go

select * from emp

Output:

emp table.jpg

Create second table:

create table dept(dep_id int,dept_name varchar(20))

go

insert into dept

select 1,'HR' union all

select 2,'Manager' union all

select 3,'Finance' union all

select 4,'Manager' union all

select 5,'Sales' union all

select 6,'Finance' union all

select 7,'Sales'

go

select * from dept

Output:

dept.jpg

Create third table:

create table employee(id int,salary int)

go

insert into employee

select 1,25000 union all

select 2,30000 union all

select 3,20000 union all

select 4,50000 union all

select 5,18000

go

select * from employee

Output:

employee table in sql.jpg

Find largest table among above three tables.

Sp_foreachtable is an undocumented Stored Procedure that is not listed in MSDN books. When we run a query using Sp_msforeachtable then we use ? instead of table name. '?' represents table name. Write following code to find largest table:

use EmployeeDetails

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FindLargestTable]') AND type in (N'U'))

DROP TABLE [dbo].[FindLargestTable]

GO

CREATE TABLE FindLargestTable

(

 [TableName] sysname,

 [RowCount] int

)

EXEC sp_MSForEachTable 'INSERT [FindLargestTable]([TableName], [RowCount]) SELECT ''?'', COUNT(*) FROM ?'

select * from FindLargestTable order by [FindLargestTable].[RowCount] desc

Output:

FindLargestTable using StoredProcedure.jpg

© 2013 dotNetheaven. All rights reserved.