How to find out the biggest table by size in a database?

Question

Post reply

How to find out the biggest table by size in a database?

sivaprasad

SSCertifiable

Points: 6183
More actions
April 25, 2005 at 6:27 am

#185732

I am looking for a SQL Query on a way to find out the biggest table by size in a database?
THA

Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

A quick and dirty way moght look like

USE NORTHWIND

GO

SELECT TOP 1

used AS "# of Pages"

, rows AS "# of Rows"

, (used * 8) / 1024 AS "# of MB"

, CAST(OBJECT_NAME(id) AS CHAR(30)) AS TableName

FROM

sysindexes

--WHERE

-- indid =1

ORDER BY

used

DESC

# of Pages # of Rows # of MB TableName

----------- ----------- ----------- ------------------------------

59 830 0 Orders

(1 row(s) affected)

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

jmcgarvey Default port Points: 1489 More actions · Answer 2

Here is a little different twist on your request.

-- Create the temp table for further querying

CREATE TABLE #temp(

ServerName varchar(30),

DBName varchar(40),

rec_id int IDENTITY (1, 1),

table_name varchar(128),

nbr_of_rows int,

data_space decimal(15,2),

index_space decimal(15,2),

total_size decimal(15,2),

percent_of_db decimal(15,12),

db_size decimal(15,2))

-- Get all tables, names, and sizes

EXEC sp_msforeachtable @command1="insert into #temp(nbr_of_rows, data_space, index_space) exec sp_mstablespace '?'",

@command2="update #temp set table_name = '?' where rec_id = (select max(rec_id) from #temp)"

-- Set the total_size and total database size fields

UPDATE #temp

SET total_size = (data_space + index_space),

db_size = (SELECT SUM(data_space + index_space) FROM #temp),

ServerName = @@ServerName,

DBName = DB_Name()

-- Set the percent of the total database size

UPDATE #temp

SET percent_of_db = (total_size/db_size) * 100

-- Get the data

-- Modify the percent_of_db int the where clause as

-- desired

SELECT

ServerName,

DBName,

table_name,

nbr_of_rows,

data_space,

index_space,

total_size,

cast(percent_of_db as decimal(5,2)) as PCNT,

db_size

FROM #temp

where percent_of_db > .99

ORDER BY total_size DESC

-- Comment out the following line if you want to do further querying

DROP TABLE #temp

Azzam Alkadi SSChasing Mays Points: 640 More actions · Answer 3

Here is another solution, that list all tables and database size.

CREATE TABLE #temp(

rec_id int IDENTITY (1, 1),

table_name varchar(128),

nbr_of_rows int,

data_space decimal(15,2),

index_space decimal(15,2),

total_size decimal(15,2),

percent_of_db decimal(15,12),

db_size decimal(15,2))

-- Get all tables, names, and sizes

EXEC sp_msforeachtable @command1="insert into #temp(nbr_of_rows, data_space, index_space) exec sp_mstablespace '?'",

@command2="update #temp set table_name = '?' where rec_id = (select max(rec_id) from #temp)"

-- Set the total_size and total database size fields

UPDATE #temp

SET total_size = (data_space + index_space), db_size = (SELECT SUM(data_space + index_space) FROM #temp)

-- Set the percent of the total database size

UPDATE #temp

SET percent_of_db = (total_size/db_size) * 100

-- Get the data

SELECT *

FROM #temp

ORDER BY total_size DESC

-- Comment out the following line if you want to do further querying

DROP TABLE #temp

Azzam.

Roy Ashbrook SSChasing Mays Points: 632 More actions · Answer 4

Here's my script:

select

so.id as [OBJECT_ID],

so.name as [OBJECT_NAME],

coalesce(j_rows.rows,0) as [ROWCOUNT],

coalesce(j_ru.sum_reserved,0) * cast(m.low as dec) / 1024 as [RESERVED (KB)],

d.data * cast(m.low as dec) / 1024 as [DATA (KB)],

(coalesce(j_ru.sum_used,0) - d.data) * cast(m.low as dec) / 1024 as [INDEX (KB)],

(coalesce(j_ru.sum_reserved,0) - coalesce(j_ru.sum_used,0)) * cast(m.low as dec) / 1024 as [UNUSED (KB)]

from

sysobjects so

-- rows

left join sysindexes j_rows

on j_rows.indid < 2 and j_rows.id = so.id

/* reserved: sum(reserved) where indid in (0, 1, 255) */

/* index: sum(used) where indid in (0, 1, 255) - data */

/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */

left join

(

select

id, sum(reserved) as sum_reserved, sum(used) as sum_used

from

sysindexes

where

indid in (0, 1, 255)

group by

id

&nbsp j_ru on j_ru.id = so.id

/*

** data: sum(dpages) where indid < 2

** + sum(used) where indid = 255 (text)

*/

left join

(

select

j_dpages.id, coalesce(j_dpages._sum,0) + coalesce(j_used._sum,0) as data

from

(

select

id, sum(dpages) as _sum

from

sysindexes

where

indid < 2

group by

id

&nbsp j_dpages left join

(

select

id, sum(used) as _sum

from

sysindexes

where

indid = 255

group by

id

&nbsp j_used on j_used.id = j_dpages.id

&nbsp d on d.id = so.id

inner join master.dbo.spt_values m

on m.number = 1 and m.type = 'E'

where

OBJECTPROPERTY(so.id, N'IsUserTable') = 1

order by

[DATA (KB)] DESC, [ROWCOUNT] ASC

Roy Ashbrook SSChasing Mays Points: 632 More actions · Answer 5

Roy Ashbrook

SSChasing Mays

Points: 632

April 26, 2005 at 4:58 pm

#555069

not sure why the ')' all show up as smileys.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Thats the forum software. It parses the text for characters to convert to smilies. Pretty annoying when posting code

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

sivaprasad SSCertifiable Points: 6183 More actions · Answer 7

Hi Azzam,

The given query does not give any records.

Please check the query

Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]