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:
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:
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:
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: