Query to identify biggest tables in database 
Author Message
 Query to identify biggest tables in database
I would like to create a SQL query that would list the top 10 largest tables
in a database and display how many rows and total bytes they are using.

Is this possible?

Thanks



Tue, 20 Jul 2004 22:41:32 GMT
 Query to identify biggest tables in database

The only guaranteed way to check number of rows is by doing a count(*).
But... you can get a (normally) very reliable estimate by looking at the
data in sysindexes.

I'm running late right now and don't have time to decode the info for you,
but you should be able to find the necessary info by reading about the
sysindexes table in Books Onlin.

--

Brian Moran
SQL Server MVP
SQL Server Magazine Columnist


Quote:
> I would like to create a SQL query that would list the top 10 largest
tables
> in a database and display how many rows and total bytes they are using.

> Is this possible?

> Thanks



Tue, 20 Jul 2004 23:10:20 GMT
 Query to identify biggest tables in database
Thank you, that was exactly what I was looking for.

I admire your use of documentation too!



Quote:
> Andy, I recently wrote a procedure to do that, but haven't tested it well
> :-) Download the attached zip file, extract the sp script, create it and
see
> how it works for you. Let me know incase of any problems. It's completely
> documented, read the comments.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/



> > I would like to create a SQL query that would list the top 10 largest
> tables
> > in a database and display how many rows and total bytes they are using.

> > Is this possible?

> > Thanks



Wed, 21 Jul 2004 01:19:20 GMT
 Query to identify biggest tables in database
Glad that it helped. Thanks for your testing, now it will make it to my
website :)
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/


Thank you, that was exactly what I was looking for.

I admire your use of documentation too!



Quote:
> Andy, I recently wrote a procedure to do that, but haven't tested it well
> :-) Download the attached zip file, extract the sp script, create it and
see
> how it works for you. Let me know incase of any problems. It's completely
> documented, read the comments.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/



> > I would like to create a SQL query that would list the top 10 largest
> tables
> > in a database and display how many rows and total bytes they are using.

> > Is this possible?

> > Thanks



Wed, 21 Jul 2004 02:05:30 GMT
 Query to identify biggest tables in database


Quote:
> I would like to create a SQL query that would list the top 10 largest
tables
> in a database and display how many rows and total bytes they are using.

> Is this possible?

> Thanks

Funny, I just dug this out the other day.  It comes with no guarantees, etc.

CREATE PROCEDURE sp_tablespaceused_u AS

/**************************************************
** sp_tablespaceused_u
**
** This will return the individual numbers for each user defined table in
the given database.
**
**
** Author: Greg d. Moore
** Date: 6/14/99
** Version: 1.00
**
**************************************************/

declare c_space insensitive scroll cursor
 for select name from sysobjects where type = 'U'









create table #results_space
(
 dbname varchar(30),
 rows varchar(30),
 reserved varchar(30),
 data varchar(30),
 indexp varchar(30),
 unused varchar(30)
)

set nocount on

open c_space



  begin
 insert into #results_space (dbname, rows, reserved, data, indexp, unused)


  end

select * from #results_space

deallocate c_space
drop table #results_space



Wed, 21 Jul 2004 09:55:19 GMT
 Query to identify biggest tables in database
Try running this:

SELECT obj.name, ind.rows, ind.dpages
FROM
 sysindexes ind,
 sysobjects obj
WHERE
 ind.indid in (0,1)
 and ind.id = obj.id
 and obj.type = "U"
ORDER BY
ind.rows desc



Quote:



> > I would like to create a SQL query that would list the top 10 largest
> tables
> > in a database and display how many rows and total bytes they are using.

> > Is this possible?

> > Thanks

> Funny, I just dug this out the other day.  It comes with no guarantees,
etc.

> CREATE PROCEDURE sp_tablespaceused_u AS

> /**************************************************
> ** sp_tablespaceused_u
> **
> ** This will return the individual numbers for each user defined table in
> the given database.
> **
> **
> ** Author: Greg d. Moore
> ** Date: 6/14/99
> ** Version: 1.00
> **
> **************************************************/

> declare c_space insensitive scroll cursor
>  for select name from sysobjects where type = 'U'









> create table #results_space
> (
>  dbname varchar(30),
>  rows varchar(30),
>  reserved varchar(30),
>  data varchar(30),
>  indexp varchar(30),
>  unused varchar(30)
> )

> set nocount on

> open c_space



>   begin
>  insert into #results_space (dbname, rows, reserved, data, indexp, unused)


>   end

> select * from #results_space

> deallocate c_space
> drop table #results_space



Sat, 31 Jul 2004 05:49:10 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Advice needed with a big, big query

2. Opening Database and Identifying Tables at Run-Time

3. Identifying table in database

4. Script to identify modified tables to map database fields

5. filtered Tables / SQL-Queries (BIG problems with the BDE)

6. Big table query question

7. SQL: query big table

8. NEEDED: BETTER JOIN PERFORMANCE ON BIG TABLE WITH BIG INDEXES (NESTED LOOPS AND MERGE JOINS)

9. Tables: How big is too big?

10. databases and how to identify system databases rather than user databases

11. How big is a big database on MSDE ?

12. URGENT - VB creates a big big database in Win98


 
Powered by phpBB® Forum Software