Tuesday, April 19, 2011

Find Biggest Tables In A Database

As a DBA, we have to maintain thousands of databases in one server then it is very difficult to track which table of which database consuming how much space for estimating the space requirement, for avoiding the sudden surprises. here i wrote sql on this to find out which table consuming more space in a particular database.
 
 Here is the Script:

/*******************************************************************
* Nareshkar Pakanati (www.mssqlfix.com)
********************************************************************/
 

declare @id int
declare @type character(2)
declare @pages int
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0) 


create table #t_space
(
objid int null,
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)
set nocount on

-- Create a cursor to loop through the user tables
declare c_tables cursor for
select id
from sysobjects
where xtype = 'U'


open c_tables

fetch next from c_tables
into @id

while @@fetch_status = 0
begin

/* Code from sp_spaceused */
insert into #t_space (objid, reserved)
select objid = @id, sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id

select @pages = sum(dpages)
from sysindexes
where indid < 2
and id = @id
select @pages = @pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @id
update #t_space
set data = @pages
where objid = @id


/* index: sum(used) where indid in (0, 1, 255) - data */
update #t_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
- data
where objid = @id

/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #t_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
where objid = @id

update #t_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @id
and objid = @id

fetch next from c_tables
into @id
end

select top 25
Table_Name = (select left(name,25) from sysobjects where id = objid),
rows = convert(char(11), rows),
reserved_KB = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),
data_KB = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),
index_size_KB = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),
unused_KB = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB')

from #t_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
order by reserved desc

drop table #t_space
close c_tables
deallocate c_tables

The output would be like below:
























Find Row Count in Table. Find Largest Table in Database. Find the largest tables in a database. Finding the biggest tables in a database. Tips and Tricks for SQL-BI: Find Largest Size Tables in a Database. How to Find Large Tables in SQL Database. SQL MAX() Function. How to find largest objects in a SQL Server database. Determing SQL Server Table Size. Find out the tables having the largest size in your database using.  How to find the largest sql index and table size. Finding out largest tables on MySQL Server. Usn's IT Blog » How to find out the (biggest) table size in MS SQL . How to find second highest value of a column in a table. Query to find number Rows. Columns. ByteSize for.

2 comments:

Post a Comment