Solved

Directory and statistics of each table in an SQL 2000 database

Posted on 2011-02-22
5
368 Views
Last Modified: 2012-05-11
I am using SQL2000 for my database, which has 178 tables and 23 GB in MDF size. (including 30+ system files).

I wish to determine a quick way to know how big is each table and how many rows it has, how much hard disk space it is taking. May I ask how to find that out?

Thank you.
0
Comment
Question by:cq27
5 Comments
 
LVL 17

Expert Comment

by:dbaSQL
ID: 34958227
Use this to find the basic details regarding your table size:

exec sp_spaceused 'tablename'

use this to find the rowcount per table:

DECLARE @SQL NVARCHAR(255)
SET @SQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')'
EXEC sp_executeSQL @SQL

SELECT
      Schema_name(t.schema_id) AS SchemaName,
  t.name                   AS TableName,
  i.rows                   as [Rows]
FROM  
      sys.tables AS t INNER JOIN sys.sysindexes AS i
    ON t.object_id = i.id AND i.indid < 2
ORDER BY
      t.name

GO
0
 

Author Comment

by:cq27
ID: 34958433
Hi there

For the table size query "exec sp_spaceused 'tablename'", is there a way to display all the user tables at a go? Otherwise it will be a tedious process to check every single table out.

For the rowcount per table, when I ran this query, I receive this:

'Schema_name' is not a recognized function name.

Could you assist in this? Thank you.
0
 
LVL 10

Expert Comment

by:John Claes
ID: 34958905
Hey,

I'm always using the folowing script :


This Script returns  :
Number of Rows  / Reserved space / Data Size / Index Size / Unused Size

The reserved Size is the Total Size of the Table ;-)

regards

poor beggar
CREATE PROCEDURE GetAllTableSizes
AS
/*
    Obtains spaced used data for ALL user tables in the database
*/
DECLARE @TableName VARCHAR(100)      --For storing values in the cursor
DECLARE @TableSchema VARCHAR(100)    --For storing values in the cursor

--Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR
FOR 
select sysobjects.[name],sys.schemas.[name]
from sysobjects
INNER JOIN sys.schemas ON sysobjects.uid  = sys.schemas.schema_id 
where  OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY

--A procedure level temp table to store the results
CREATE TABLE #TempTable
(
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)

--Open the cursor
OPEN tableCursor

--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName, @TableSchema

--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
    --Dump the results of the sp_spaceused query to the temp table
	declare @TableFullName varchar(201)
	set @TableFullName= @TableSchema + '.' + @TableName
print @TableFullName
    INSERT  #TempTable
        EXEC sp_spaceused @TableFullName

    --Get the next table name
    FETCH NEXT FROM tableCursor INTO @TableName, @TableSchema
END

--Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor

--Select all records so we can use the reults
SELECT * 
FROM #TempTable

--Final cleanup!
DROP TABLE #TempTable

GO

Open in new window

0
 
LVL 17

Accepted Solution

by:
dbaSQL earned 25 total points
ID: 34961567
>>is there a way to display all the user tables at a go?
Yes, do this:

USE yourdatabasename
GO

EXEC sp_msforeachtable 'exec sp_spaceused [?]'
GO

The error on schema_name is likely because you're on v2000.  I missed that.  You can try either of these items, though the first is not guaranteed to be accurate.  I'd use the second one.
#1
SELECT SO.Name, SI.rows 
FROM sysindexes SI, 
    SysObjects SO 
WHERE SI.id = SO.ID 
    AND SI.indid < 2
    AND SO.Type = 'U'
order by so.name


#2
create table tablecounts (
  tablename varchar(255) not null,
  recordcount int not null
)
go

exec sp_msforeachtable "insert into tablecounts select '?' as tablename, count(*) from ?"

select * from tablecounts
drop table tablecounts

Open in new window

0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 25 total points
ID: 34963501
Or you can simply do this from EM:
1. Right-click on the database.
2. Select View.
3. Select Taskpad.
4. Click on the Table Info tab.

You will get for each table the following information:
Rows
Size
Index Size
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

On July 14th 2015, Windows Server 2003 will become End of Support, leaving hundreds of thousands of servers around the world that still run this 12 year old operating system vulnerable and potentially out of compliance in many organisations around t…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question